怎么簡化查询语句
日期：2014-05-18 浏览次数：20569 次
如何簡化查询语句
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
怎么把这两次查询并成一次?
------解决方案--------------------
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
