A表:
popedom
1100
B表
menuname menutitle popedom
A 账单登记 0
A1 入库单 0
A2 \- 0
A3 领料单 0
B 产品结构 0
B1 产品类型修改 0
B2 \- 0
B3 生产单号修改 0
希望更新B表为:
menuname menutitle popedom
A 账单登记 1
A1 入库单 1
A2 \- 1
A3 领料单 1
B 产品结构 0
B1 产品类型修改 0
B2 \- 0
B3 生产单号修改 0
也就是说,把A表的1100分别按字符读到B表中满足
len(menuname)>1 and menutitle<>'\-'的记录的popedom中
然后只要这一类有为1的记录,这一类的len(menuname)=1 or menutitle='\-'的popedom也就更新为1
我现在是用两个循环实现,不知道是否可用一条或几条SQL实现,效率高些
---------------------------------------------------------------
问题:
如果记录次序为以下,怎么办?
A 账单登记 0
A1 入库单 0
A2 \- 0
B 产品结构 0
B1 产品类型修改 0
B2 \- 0
B3 生产单号修改 0
A3 领料单 0
select menuname,menutitle,identity(int,1,1) ID into #t1 from B
where len(menuname)>1 and menutitle<>'\-'
order by menuname
select menuname,menutitle,substring(A.popedom,#t1.ID,1) popedom into #t2
from #t1,A
update B
set popedom=#t2.popedom
from B,#t2
where B.menuname=#t2.menuname
update B1
set popedom=1
from b b1
where exists (
seelct * from b where (len(menuname)=1 or menutitle='\-') and popedom=1 and left(menuname,1)=left(b1.menuname,1)
)
---------------------------------------------------------------
select menuname,menutitle,identity(int,1,1) ID into #t1 from menus
where len(menuname)>1 and menutitle<>'\-'
order by manuname
select menuname,menutitle,substring(A.popedom,#t1.ID,1) popedom into #t2 from #t1,passwords A
update B
set popedom=#t2.popedom
from menus B,#t2
where B.menuname=#t2.menuname or
(#t2.popedom=1 and left(B.menuname,1)=left(#t2.menuname,1) and (len(B.menuname)=1 or B.menutitle='\-'))
你举的例子,好象有不对
好象为
1000
更新B表为:
menuname menutitle popedom
A 账单登记 1
A1 入库单 1
A2 \- 1
A3 领料单 0
---------------------------------------------------------------
清楚了。加一个条件就行了
declare @i int
set @i=0
update B
set popedom=substring(A.popedom,@i,1),@i=@i+1
from B,A where len(menuname)>1 and menutitle<>'\-'
update x
set popedom=1
from b x,b y where left(x.menuname,1)=left(y.menuname,1) and y.popedom=1 and (len(x.menuname)=1 or x.menutitle='\-')
select * from B