一个SQL的写法,把A表的一个字段值按位赋给B表的各列

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

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus