|
SELECT t1.DAY,(case when t2.day=t1.day then t2.num else t1.num end) countNum
FROM (
select to_char(sysdate-level+1,'yyyy-mm-dd') DAY,0 NUM from dual CONNECT by level<=7
) t1
LEFT JOIN
(
SELECT COUNT(1) NUM, TO_CHAR(add_time,'YYYY-MM-DD') DAY
FROM T_AQJB
WHERE TO_CHAR(add_time,'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD') AND TO_CHAR(add_time,'YYYY-MM-DD') >= TO_CHAR(SYSDATE-6,'YYYY-MM-DD')
GROUP BY TO_CHAR(add_time,'YYYY-MM-DD')
)t2 ON T2.DAY=T1.DAY
ORDER BY t1.DAY DESC
假如要统计周一到周日。
select
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 1,'yyyy-mm-dd')) then 1 else 0 end) ) c_mon,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 2,'yyyy-mm-dd')) then 1 else 0 end) ) c_tue,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 3,'yyyy-mm-dd')) then 1 else 0 end) ) c_wed,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 4,'yyyy-mm-dd')) then 1 else 0 end) ) c_thu,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 5,'yyyy-mm-dd')) then 1 else 0 end) ) c_fri,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 6,'yyyy-mm-dd')) then 1 else 0 end) ) c_sat,
(sum( case when (to_char(CREATE_TIME,'yyyy-mm-dd')) =(to_char(trunc(sysdate, 'd') + 7,'yyyy-mm-dd')) then 1 else 0 end) ) c_sun
from 表名 | 
转播
