日期:2014-05-18  浏览次数:20511 次

如何簡化查询语句
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 boxqty
from view_goodsrunpart a  
 where (1=1)  
group by a.orderno)aa
where 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 boxqty
from view_goodsrunpart a  
group by a.orderno
order by a.orderno
having  sum(isnull(a.runqty,0))>0