摆脱那位大侠帮忙优化一下下面的sql语句,谢谢了
SELECT
rdo.pk_invbasdoc,rdo.pk_stordoc
--+ groupStr
,max(inv.invname) as invname,max(wh.storname) as whname,
SUM (nvl(ntermbeginnum1,0.0)) ntermbeginnum,
SUM (nvl(nterminnum1,0.0)) nterminnum,
SUM (nvl(ntermoutnum1,0.0)) ntermoutnum,
SUM (nvl(ntermbadnuml,0.0)) ntermbadnum,
SUM (nvl(ntermbeginnum1,0.0) + nvl(nterminnum1,0.0)- nvl(ntermoutnum1,0.0)- nvl(ntermbadnuml,0.0)) ntermonhandnum
FROM(
SELECT pk_invbasdoc,pk_stordoc,
SUM (nvl(ninnum,0.0)-nvl(noutnum,0.0)) ntermbeginnum1 ,
0.0 nterminnum1 ,
0.0 ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
where 1=1
and kp.billdate < '2008-11-01'
group by pk_invbasdoc,pk_stordoc
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
SUM (ninnum) nterminnum1 ,
0.0 ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
left join bd_reason r on kp.back_reason=r.untrerea_id
where (nvl(ninnum,0.0)>0 or (nvl(ninnum,0.0)<0 and nvl(r.reason,'11')!='坏机退回'))
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(ninnum) IS NOT NULL))
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
0.0 nterminnum1 ,
SUM (noutnum) ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
where 1=1
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(noutnum) IS NOT NULL))
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
0.0 nterminnum1 ,
0.0 ntermoutnum1 ,
SUM (abs(ninnum)) ntermbadnuml
from view_imdetail kp
left join bd_reason r on kp.back_reason=r.untrerea_id
where nvl(ninnum,0.0)<0 and nvl(r.reason,'11')='坏机退回'
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(ninnum) IS NOT NULL))
) rdo
join bd_invbasdoc inv on (inv.pk_invbasdoc=rdo.pk_invbasdoc
)
join bd_invcl invcl on ( inv.pk_invcl = invcl.pk_invcl
)
join bd_stordoc wh on (wh.pk_stordoc=rdo.pk_stordoc
)
GROUP BY
rdo.pk_invbasdoc,rdo.pk_stordoc
--+ groupStr
, invcl.invclasscode, inv.invcode, inv.invname
order by invcl.invclasscode, inv.invname
SELECT
rdo.pk_invbasdoc,rdo.pk_stordoc
--+ groupStr
,max(inv.invname) as invname,max(wh.storname) as whname,
SUM (nvl(ntermbeginnum1,0.0)) ntermbeginnum,
SUM (nvl(nterminnum1,0.0)) nterminnum,
SUM (nvl(ntermoutnum1,0.0)) ntermoutnum,
SUM (nvl(ntermbadnuml,0.0)) ntermbadnum,
SUM (nvl(ntermbeginnum1,0.0) + nvl(nterminnum1,0.0)- nvl(ntermoutnum1,0.0)- nvl(ntermbadnuml,0.0)) ntermonhandnum
FROM(
SELECT pk_invbasdoc,pk_stordoc,
SUM (nvl(ninnum,0.0)-nvl(noutnum,0.0)) ntermbeginnum1 ,
0.0 nterminnum1 ,
0.0 ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
where 1=1
and kp.billdate < '2008-11-01'
group by pk_invbasdoc,pk_stordoc
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
SUM (ninnum) nterminnum1 ,
0.0 ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
left join bd_reason r on kp.back_reason=r.untrerea_id
where (nvl(ninnum,0.0)>0 or (nvl(ninnum,0.0)<0 and nvl(r.reason,'11')!='坏机退回'))
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(ninnum) IS NOT NULL))
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
0.0 nterminnum1 ,
SUM (noutnum) ntermoutnum1 ,
0.0 ntermbadnuml
from view_imdetail kp
where 1=1
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(noutnum) IS NOT NULL))
UNION ALL (
SELECT pk_invbasdoc,pk_stordoc,
0.0 ntermbeginnum1 ,
0.0 nterminnum1 ,
0.0 ntermoutnum1 ,
SUM (abs(ninnum)) ntermbadnuml
from view_imdetail kp
left join bd_reason r on kp.back_reason=r.untrerea_id
where nvl(ninnum,0.0)<0 and nvl(r.reason,'11')='坏机退回'
and kp.billdate >= '2008-11-01'
and kp.billdate <= '2008-11-15'
group by pk_invbasdoc,pk_stordoc
HAVING (SUM(ninnum) IS NOT NULL))
) rdo
join bd_invbasdoc inv on (inv.pk_invbasdoc=rdo.pk_invbasdoc
)
join bd_invcl invcl on ( inv.pk_invcl = invcl.pk_invcl
)
join bd_stordoc wh on (wh.pk_stordoc=rdo.pk_stordoc
)
GROUP BY
rdo.pk_invbasdoc,rdo.pk_stordoc
--+ groupStr
, invcl.invclasscode, inv.invcode, inv.invname
order by invcl.invclasscode, inv.invname
解决方案 »
- oracle 同一台主机上如何rman恢复具有相同数据库名和实例名的3个数据库?在线等!!
- 在oracle中添加24小时制的时间
- OCI编程
- 学习数据库 找份工作 需要学到什么程度
- 请问plsql如何能一次显示全部查询结果
- 请教一个sql语句,请各位大侠务必帮帮小弟
- 我机器里已安装windowserver2003+sqlserver2000,我现在下载了oracle9i,文件名是OUI220180.zip,大小是46295kb,另一文件是9203WIN2K3.zip
- 如何选择时间段查询数据
- 100分求教汇总统计方法
- Oracle11g imp 命令 导入dmp:IMP-00017: 由于 ORACLE 错误 2153 请大神帮忙。
- Oracle Sql Developer 调试程序报错,大家帮帮忙!!
- 在oracle中怎么实现用存储过程自动调用exe文件?
既然是可以union的查询,而且数据源都是一样的(同个表格),那么就很容易了。
你的语句太长,不想整理,就举个例子吧,能理解就理解!
例如
select cola ,sum(s1) from table where xx group by cola
union all
select cola ,sum(s2) from table where xxs cola
可以如此写
select cola ,sum(case when xx then s1 when xxs then s2 else 0 end)
from table where xxx or xxs group by cola. 大意如此,无意深入分析lz的sql.