22,207
社区成员
发帖
与我相关
我的任务
分享
create table #temp
(ke nvarchar(20),
d1 int default 0)
insert into #temp
select '1课1',0 union
select '2课1',0
select * from #temp 得到
1课1 0
2课1 0
update #temp set d1=(select sum(fgrd_qty) from fgrd_det
join fgr_mstr on fgrd_fgr=fgr_fgr
where ke='1课1' and exists(select 1 from wr_route where wr_mch='1050'
and wr_nbr=fgrd_nbr and wr_lot=fgrd_lot) and left(fgrd_char2,2)='1课' and fgr_date='20100402')
update #temp set d1=(select sum(fgrd_qty) from fgrd_det
join fgr_mstr on fgrd_fgr=fgr_fgr
where ke='2课1' and exists(select 1 from wr_route where wr_mch='1050'
and wr_nbr=fgrd_nbr and wr_lot=fgrd_lot) and left(fgrd_char2,2)='1课' and fgr_date='20100401')
--两者一起执行。。得到结果
1课1 NULL
2课1 7586
正常1课1是有值的。。
看看是不是要这样的效果
--d1为null或0时才更新
update #temp set d1= isnull((select sum(fgrd_qty) from fgrd_det
join fgr_mstr on fgrd_fgr=fgr_fgr
where ke='1课1' and exists(select 1 from wr_route where wr_mch='1050' and ke=#temp.ke--加上这个
and wr_nbr=fgrd_nbr and wr_lot=fgrd_lot) and left(fgrd_char2,2)='1课' and fgr_date='20100402'),d1)
where ISNULL(d1,0)=0
或
update a set d1= b.fgrd_qty
from #temp a,
(select ke, sum(fgrd_qty) as fgrd_qty from fgrd_det join fgr_mstr on fgrd_fgr=fgr_fgr
where ke='1课1' and exists(select 1 from wr_route where wr_mch='1050'
and wr_nbr=fgrd_nbr and wr_lot=fgrd_lot) and left(fgrd_char2,2)='1课' and fgr_date='20100402' group by ke)b
where a.ke=b.ke and isnull(a.d1,0)=0
--left(fgrd_char2,2)=LEFT(#temp.ke,2)/ke=#temp.ke--把这两个地方改改
update #temp set d1=(select sum(fgrd_qty) from fgrd_det
join fgr_mstr on fgrd_fgr=fgr_fgr
where ke=#temp.ke and exists(select 1 from wr_route where wr_mch='1050'
and wr_nbr=fgrd_nbr and wr_lot=fgrd_lot) and left(fgrd_char2,2)=LEFT(#temp.ke,2) and fgr_date='20100402'
最好给出完整的表结构,测试数据,计算方法和正确结果.