select table1.id,sum(table1.overqty),sum(table1.wasterqty) from table1,table2
where (table1.wastergoods=table2.goods) or ( isnull(table1.wastergoods) and isnull(table2.goods) )
group by id
where (table1.wastergoods=table2.goods) or ( isnull(table1.wastergoods) and isnull(table2.goods) )
group by id
where (table1.wastergoods=table2.goods) or ((table1.wastergoods is null ) and (table2.goods is null) )
group by id
select 1.id,sum(1.overqty),sum(1.wasterqty) from table1 as 1 left join table2 as 2
on 1.wastergoods=2.goods
group by 1.id
或:
select 1.id,
sum(case when 1.overqty is not null then 1.overqty else 0 end),
sum(case when 1.wasterqty is not null then 1.wasterqty else 0 end)
from table1 as 1 left join table2 as 2
on 1.wastergoods=2.goods
group by 1.id
你要的数据与table2表没有任何关系?
select table1.id,sum(table1.overqty),sum(table1.wasterqty*table2.long)
from table1,table2
where table1.wastergoods=table2.goods
group by id
as qty,sum(wasterqty)+(select isnull(sum(wasterqty),0) from #tmp1 where (wastergoods is null) and (tid=a.tid))
as wasterqty
from #tmp1 a,#tmp2 b where a.wastergoods=b.goods
group by a.tid
a b c b d e c f h
1 2 3 2 5 6 3 2 2
1 0 null 7 1 1
1 2 7
select a.a ,a.b*c.f from a,b,c where
(a.b=b.b) and (a.c=c.c)
如何解决丢记录问题