1. 统计近7日的数据 sql
SELECT
days.createTime createTime ,
nvl (m.COUNT, 0) count
FROM
(SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime
FROM
DUAL connect BY LEVEL <= 7) days
LEFT JOIN
(SELECT
to_char (HEALTH_USER.CREATE_DATE, 'yyyy-mm-dd') createTime,
nvl (COUNT(HEALTH_USER.ID), 0) count
FROM
HEALTH_USER
GROUP BY to_char (HEALTH_USER.CREATE_DATE, 'yyyy-mm-dd')) m
ON days.createTime = m.createTime
GROUP BY days.createTime , m.count
ORDER BY days.createTime
2.统计一年中每个月的数据
select
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'01',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums01,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'02',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums02,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'03',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums03,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'04',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums04,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'05',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums05,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'06',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums06,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'07',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums07,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'08',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums08,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'09',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums09,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'10',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums10,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'11',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums11,
sum(decode(to_char(HEALTH_USER.CREATE_DATE,'mm'),'12',NVL(COUNT(HEALTH_USER.ID), 0),0)) nums12
from
HEALTH_USER
where
to_char(HEALTH_USER.CREATE_DATE,'yyyy')='2018' AND HEALTH_USER.STATION_ID='330965C6BAEE4DD58513D4DC229FF1A0'
GROUP BY
to_char(HEALTH_USER.CREATE_DATE,'mm'),'01',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'02',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'03',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'04',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'05',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'06',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'07',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'08',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'09',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'10',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'11',
to_char(HEALTH_USER.CREATE_DATE,'mm'),'12'