select a.orderno,
sum(isnull(a.inqty,0)) as inqty,sum(isnull(a.outqty,0)) as outqty,
sum(isnull(a.runqty,0)) as runqty,a.unit,a.price,a.curr,
sum(isnull(a.runqty,0))*a.price as priceamnt,SUM(runBoxqty) AS boxqty
into #tmp01
from view_goodsrunpart a
where (1=1)
group by a.orderno
order by a.orderno
select * from #tmp01 where runqty>0 order by orderno,isbn
drop table #tmp01
怎么把这两次查询并成一次?
------解决方案--------------------
- SQL code
select * from (select a.orderno,sum(isnull(a.inqty,0)) as inqty,sum(isnull(a.outqty,0)) as outqty,sum(isnull(a.runqty,0)) as runqty,a.unit,a.price,a.curr,sum(isnull(a.runqty,0))*a.price as priceamnt,SUM(runBoxqty) AS boxqtyfrom view_goodsrunpart a where (1=1) group by a.orderno)aawhere runqty>0 order by orderno,isbn
------解决方案--------------------
- SQL code
select a.orderno, sum(isnull(a.inqty,0)) as inqty,sum(isnull(a.outqty,0)) as outqty, sum(isnull(a.runqty,0)) as runqty,a.unit,a.price,a.curr, sum(isnull(a.runqty,0))*a.price as priceamnt,SUM(runBoxqty) AS boxqtyfrom view_goodsrunpart a group by a.ordernoorder by a.ordernohaving sum(isnull(a.runqty,0))>0