SELECT A.RIQI,A.HEDANSUM,B.FAHUOSUM,C.YADANSUM,d.allsums FROM
(SELECT TO_DATE(SUBSTR(MODEL.CHECKTIME,0,8),'YYYY-MM-DD')AS RIQI ,COUNT(*)AS HEDANSUM
FROM ORDERCHECKINFO MODEL WHERE MODEL.CHECKTIME IS NOT NULL
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')>=TO_DATE('2009-01-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2009-08-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
GROUP BY SUBSTR(MODEL.CHECKTIME,0,8)) A
LEFT JOIN
(SELECT TO_DATE(SUBSTR(MODEL.CHECKTIME,0,8),'YYYY-MM-DD')AS RIQI ,COUNT(*)AS FAHUOSUM
FROM ORDERCHECKINFO MODEL WHERE MODEL.ISCHECKED='1'
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')>=TO_DATE('2009-01-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2009-08-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
GROUP BY SUBSTR(MODEL.CHECKTIME,0,8)) B
ON A.RIQI = B.RIQI LEFT JOIN
(SELECT TO_DATE(SUBSTR(MODEL.CHECKTIME,0,8),'YYYY-MM-DD')AS RIQI ,COUNT(*)AS YADANSUM
FROM ORDERCHECKINFO MODEL WHERE MODEL.ISCHECKED ='0'
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')>=TO_DATE('2009-01-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2009-08-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
GROUP BY SUBSTR(MODEL.CHECKTIME,0,8)) C
ON A.RIQI = C.RIQI LEFT JOIN
(select 日期 ,count(*)as allsums from(
(select 日期 ,orderid,sums from( select model.orderid,TO_DATE(substr(model.checktime,0,8),'yyyy-mm-dd')as 日期,
count(*)as sums from ordercheckinfo model where 1=1
and To_DATE(substr(model.checktime,0,14),'yyyy-mm-dd hh24:mi:ss')>=To_DATE('2009-01-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
and To_DATE(substr(model.checktime,0,14),'yyyy-mm-dd hh24:mi:ss')<=To_DATE('2009-08-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
group by orderid,substr(model.checktime,0,8)) where sums>1))group by 日期) d
on a.RIQI=d.日期 ORDER BY RIQI
或者产生 10046 和10053 的trace文件
COUNT(1) HEDANSUM,
COUNT(CASE WHEN MODEL.ISCHECKED='1' THEN 1 ELSE 0 END) FAHUOSUM,
COUNT(CASE WHEN MODEL.ISCHECKED='0' THEN 1 ELSE 0 END) YADANSUM,
--COUNT() ALLSUMS
FROM ORDERCHECKINFO MODEL
WHERE MODEL.CHECKTIME IS NOT NULL
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS') >= TO_DATE('2009-01-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(SUBSTR(MODEL.CHECKTIME,0,14),'YYYY-MM-DD HH24:MI:SS') <= TO_DATE('2009-08-01 11:23:54','YYYY-MM-DD HH24:MI:SS')
GROUP BY SUBSTR(MODEL.CHECKTIME,0,8);第4个不好解决,可能还是需要左连接,期待下面好的解决方案
为什么运行出来的结果都相同呢?
这个结果只有日期和HEDANSUM的结果是正确的
把0换成null
日期那个条件也可以简化下
select A.*,B.allsums from
(SELECT TO_DATE(SUBSTR(MODEL.CHECKTIME,0,8),'YYYY-MM-DD') RIQI,
COUNT(1) HEDANSUM,
COUNT(CASE WHEN MODEL.ISCHECKED='1' THEN 1 ELSE null END) FAHUOSUM,
COUNT(CASE WHEN MODEL.ISCHECKED='0' THEN 1 ELSE null END) YADANSUM,
--COUNT() ALLSUMS
FROM ORDERCHECKINFO MODEL
WHERE SUBSTR(MODEL.CHECKTIME,0,14) between '20090101112354' and '20090801112354'
GROUP BY SUBSTR(MODEL.CHECKTIME,0,8)) A
left outer join
(select 日期 ,count(case sums when 1 then null else 1 end)as allsums from(
select model.orderid,TO_DATE(substr(model.checktime,0,8),'yyyy-mm-dd')as 日期,
count(*)as sums from ordercheckinfo model
where substr(model.checktime,0,14) between '20090101112354' and '20090801112354'
group by orderid,substr(model.checktime,0,8)) group by 日期) B
on A.RIQI=B.日期 ORDER BY RIQI;