统计每七天数量oracle,oracle 统计最近七天的数据

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 表名