select '合 计',sum(t.zgjl),sum(t.zbjl),sum(t.zqjl) ,sum(t.xljl),sum(t.skjl),sum(t.bgjl),sum(t.bqjl),sum(t.bgjl2),
sum(t.pxjl),sum(t.ccjl),sum(t.xjjl),sum(t.jdjl),sum(t.wyjl),sum(t.xxjl), (sum(t.zgjl)+sum(t.pxjl)+sum(t.ccjl)+sum(t.xjjl)+sum(t.jdjl)+sum(t.wyjl)+sum(t.xxjl)) from (select code_mean as dwmc, (select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and
b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in
('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0203' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xjjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0204' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) jdjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0207' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) wyjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0208' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xxjl
from zb02_jgmc where code like '500107%' ) t
union all select t.dwmc,t.zgjl,t.zbjl,t.zqjl,t.xljl,t.skjl,t.bgjl,
t.bqjl,t.bgjl2,t.pxjl,t.ccjl,t.xjjl,t.xjjl,t.wyjl,t.xxjl,
(zbjl+zqjl+xljl+skjl+bgjl+bqjl+bgjl2+pxjl+ccjl+xjjl+jdjl+wyjl+xxjl) as hz
from (select code_mean as dwmc,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0203' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xjjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0204' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) jdjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0207' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) wyjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0208' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xxjl
from zb02_jgmc where code like '500107%' ) t
查询结果太慢了。希望高手指点。万分感谢!
sum(t.pxjl),sum(t.ccjl),sum(t.xjjl),sum(t.jdjl),sum(t.wyjl),sum(t.xxjl), (sum(t.zgjl)+sum(t.pxjl)+sum(t.ccjl)+sum(t.xjjl)+sum(t.jdjl)+sum(t.wyjl)+sum(t.xxjl)) from (select code_mean as dwmc, (select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and
b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in
('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0203' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xjjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0204' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) jdjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0207' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) wyjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0208' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xxjl
from zb02_jgmc where code like '500107%' ) t
union all select t.dwmc,t.zgjl,t.zbjl,t.zqjl,t.xljl,t.skjl,t.bgjl,
t.bqjl,t.bgjl2,t.pxjl,t.ccjl,t.xjjl,t.xjjl,t.wyjl,t.xxjl,
(zbjl+zqjl+xljl+skjl+bgjl+bqjl+bgjl2+pxjl+ccjl+xjjl+jdjl+wyjl+xxjl) as hz
from (select code_mean as dwmc,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0203' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xjjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0204' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) jdjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0207' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) wyjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199<'5' and b.zgzt='0208' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xxjl
from zb02_jgmc where code like '500107%' ) t
查询结果太慢了。希望高手指点。万分感谢!
rq字段如果有索引,使用to_char(b.rq,'yyyy-MM-dd')将使索引用不上;
建议使用临时表优化查询,避免过多嵌套子查询;考虑充分利用索引;如无索引,建立索引。
这段出现了2次
可改成
with t as(select code_mean as dwmc,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and b.zgzt in('0101','0102','0103','0104','0205','0206','0105')) zgjl ,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0102' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zbjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0103' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) zqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0205' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xljl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0206' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) skjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0101' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0104' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bqjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0105' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) bgjl2,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0201' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) pxjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0202' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) ccjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0203' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xjjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0204' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) jdjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0207' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) wyjl,
(select count(*) from t_dtjl_jldd b, a01 a where a.leader_code=b.leader_code and a.a0199 <'5' and b.zgzt='0208' and b.szdwdm=code and to_char(b.rq,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')) xxjl
from zb02_jgmc where code like '500107%' )select '合 计',sum(t.zgjl),sum(t.zbjl),sum(t.zqjl) ,sum(t.xljl),sum(t.skjl),sum(t.bgjl),sum(t.bqjl),sum(t.bgjl2),
sum(t.pxjl),sum(t.ccjl),sum(t.xjjl),sum(t.jdjl),sum(t.wyjl),sum(t.xxjl),
(sum(t.zgjl)+sum(t.pxjl)+sum(t.ccjl)+sum(t.xjjl)+sum(t.jdjl)+sum(t.wyjl)+sum(t.xxjl))
from t
union all
select t.dwmc,t.zgjl,t.zbjl,t.zqjl,t.xljl,t.skjl,t.bgjl,
t.bqjl,t.bgjl2,t.pxjl,t.ccjl,t.xjjl,t.xjjl,t.wyjl,t.xxjl,
(zbjl+zqjl+xljl+skjl+bgjl+bqjl+bgjl2+pxjl+ccjl+xjjl+jdjl+wyjl+xxjl) as hz
from t