商品编号 商品名称 包装规格 整单位 散单位 价钱1 价钱2 打印选择
14101 桂竹园5kg片甜心3+2 3 箱 箱 36 NULL NULL
14105 桂竹园5kg片情侣花 箱 箱 NULL 32 NULL
生夹心
14106 桂竹园5kg片冰啤梳打(芝麻)箱 箱 555 34 NULL这就是部分的结果,表中的价钱有可能有很多,是循环得来(select * from #t)时还没知列名,请教如何将里面的null改为0和在代码去除游标。
14101 桂竹园5kg片甜心3+2 3 箱 箱 36 NULL NULL
14105 桂竹园5kg片情侣花 箱 箱 NULL 32 NULL
生夹心
14106 桂竹园5kg片冰啤梳打(芝麻)箱 箱 555 34 NULL这就是部分的结果,表中的价钱有可能有很多,是循环得来(select * from #t)时还没知列名,请教如何将里面的null改为0和在代码去除游标。
把游标中的下面语句,先提出在游标外放到一个临时表里
select rowid=identity(int,1,1),d.name,price into #t1
from t_bs_product_detail as a
left join t_codes as b on b.id=a.bs_price_id
left join t_bs_product as d on d.id=a.bs_product_id
left join #t as c on c.商品编号=b.code
insert into #t(商品编号,商品名称,包装规格,整单位,散单位)
select a.code as '商品编号',a.name as '商品名称',a.packstandard as '包装规格',c.name as '整单位',b.name as '散单位'
from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id declare @colname varchar(50),@rowid int,@name varchar(50),@price decimal(18,2)
declare cur cursor for
select name from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(50) default(0) ')
select rowid=identity(int,1,1),d.name,price into #t1
from t_bs_product_detail as a
left join t_codes as b on b.id=a.bs_price_id
left join t_bs_product as d on d.id=a.bs_product_id
left join #t as c on c.商品编号=b.code
where b.name=@colname exec('update a set '+@colname+'=isnull(b.price,0) from #t a,#t1 b where a.商品名称=b.name') if exists(select name,crdate from tempdb.dbo.sysobjects where name like '#t1%')
drop table #t1
fetch next from cur into @colname
end
alter table #t add 打印选择 varchar(10) select * from #t
deallocate cur
drop table #t
insert into #t(商品编号,商品名称,包装规格,整单位,散单位)
select a.code as '商品编号',a.name as '商品名称',a.packstandard as '包装规格',c.name as '整单位',b.name as '散单位'
from t_bs_product as a
left join t_codes b on a.bs_unit_dispel_id =b.id
left join t_codes c on a.bs_unit_full_id =c.id declare @colname varchar(50),@rowid int,@name varchar(50),@price decimal(18,2)
declare cur cursor for
select name from t_codes where type=217
open cur
fetch next from cur into @colname
while @@fetch_status=0
begin
exec('alter table #t add '+@colname+' varchar(50) default(0) not null')
select rowid=identity(int,1,1),d.name,price into #t1
from t_bs_product_detail as a
left join t_codes as b on b.id=a.bs_price_id
left join t_bs_product as d on d.id=a.bs_product_id
left join #t as c on c.商品编号=b.code
where b.name=@colname exec('update a set '+@colname+'=isnull(b.price,0) from #t a,#t1 b where a.商品名称=b.name') if exists(select name,crdate from tempdb.dbo.sysobjects where name like '#t1%')
drop table #t1
fetch next from cur into @colname
end
alter table #t add 打印选择 varchar(10) select * from #t
deallocate cur
drop table #t
from t_bs_product_detail as a
left join t_codes as b on b.id=a.bs_price_id
left join t_bs_product as d on d.id=a.bs_product_id
left join #t as c on c.商品编号=b.code
where b.name=@colname
这一段挺难理解的 反正基于#t表进行数据关联 但有个问题 #t1中根本就没有用到#t的字段 你干吗也要放进去关联?逻辑大致是根据type选出列 '恶'交出不同的结果集 然后遍历(通过商品名称)来更新原始表新添列数值 不知理解正确否?