oracle最近一周、最近一月sql:
最近一周:
SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime
FROM
DUAL connect BY LEVEL <= 7
最近一月:
SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime
FROM
DUAL connect BY LEVEL <= sysdate-(add_months(sysdate,-1))
近一个月数据,每日都要显示,无数据补0:
select days.createTime,nvl(m.censusCount,0) as censusCount,nvl(m.samplingCount,0) as samplingCount,nvl(m.inhospitalCount,0) as inhospitalCount
from
( SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime
FROM
DUAL connect BY LEVEL <= sysdate-(add_months(sysdate,-1))) days
LEFT JOIN
(
SELECT
COUNT (distinct decode(RECOG_BUSI,11,decode(STATUS,11,MEDICAL_ID,12,MEDICAL_ID))) as censusCount,
COUNT (distinct decode(RECOG_BUSI,10,MEDICAL_ID)) as samplingCount,
COUNT (distinct decode(RECOG_BUSI,11,MEDICAL_ID)) as inhospitalCount,
TO_CHAR (CREATE_TIME, 'YYYY-MM-dd') createTime
FROM
t_plan
where create_time > add_months(sysdate,-1)
GROUP BY
TO_CHAR (CREATE_TIME, 'YYYY-MM-dd') ORDER BY TO_CHAR (CREATE_TIME, 'YYYY-MM-dd') ASC
) m
on days.createTime = m.createTime
GROUP BY days.createTime ,m.censusCount,m.samplingCount,m.inhospitalCount
ORDER BY days.createTime