统计一年中每个月数据 统计末次上传的数据 统计近七日的数据

1.统计一年中每个月数据 
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= #{stationId}
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'

2. 统计末次上传的数据

2.1先把时间查出来,然后时间倒叙 去第一条时间格式

SELECT
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='dahuoquan'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) aa
WHERE rownum=1) as dahuoquan
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='jinniu'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as jinniu
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='gangtie'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as gangtie
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='nandaguo'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as nandaguo
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='licun'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as licun
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='quanxi'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as quanxi
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='zhangkuan'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as zhangkuan
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='zhonghua'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as zhonghua
,
(SELECT * FROM (SELECT TO_CHAR(HEALTH_USER.CREATE_DATE,'yyyy-MM-dd')  FROM HEALTH_USER  ,SYS_STATION WHERE HEALTH_USER.STATION_ID=SYS_STATION.ID AND SYS_STATION.REMARK='zhongxin'
ORDER BY HEALTH_USER.CREATE_DATE DESC ) bb
WHERE rownum=1) as zhongxin
from dual

3. 统计近七日的数据

days.createTime createTime ,
nvl (m.COUNT, 0) count
FROM
(SELECT
to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') createTime
FROM
DUAL connect BY LEVEL <![CDATA[<=]]> 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

4.例如查询最近5天的数据：
select  * from HEALTH_USER where CREATE_DATE>=trunc(sysdate - 5)