按天统计,group by service_opentime即可。 按月统计,group by to_char(service_opentime,'yyyy-mm') 按周统计,group by to_char(service_opentiome,'IW')全年中的每一天,使用下面sql进行左关联 SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE FROM (SELECT to_date(to_char(SYSDATE,'yyyy') || '-01-01','yyyy-mm-dd') SDATE, to_date(to_char(SYSDATE,'yyyy') || '-12-31','yyyy-mm-dd') EDATE FROM DUAL) T CONNECT BY SDATE + (ROWNUM - 1) <= EDATE;
SELECT T1.DDATE, T2.SS, T2.CC FROM (SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE FROM (SELECT TO_DATE('2014-04-01', 'yyyy-mm-dd') SDATE, TO_DATE('2014-04-30', 'yyyy-mm-dd') EDATE FROM DUAL) T CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) T1, (SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE, SUM(1) SS, COUNT(1) CC FROM T_AGENT_SERVICE_MSISDN WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04' GROUP BY SERVICE_OPENTIME) T2 WHERE T1.DDATE = T2.SDATE(+)这个是将一个月的全遍历,就是即使表中没有2号的数据,也能有一条统计记录。如果没有这个需求的话,直接用下面的就行了: SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE, SUM(1) SS, COUNT(1) CC FROM T_AGENT_SERVICE_MSISDN WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04' GROUP BY SERVICE_OPENTIME
SELECT T1.DDATE, T2.SS, T2.CC FROM (SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE FROM (SELECT TO_DATE('2014-04-01', 'yyyy-mm-dd') SDATE, TO_DATE('2014-04-30', 'yyyy-mm-dd') EDATE FROM DUAL) T CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) T1, (SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE, SUM(1) SS, COUNT(1) CC FROM T_AGENT_SERVICE_MSISDN WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04' GROUP BY SERVICE_OPENTIME) T2 WHERE T1.DDATE = T2.SDATE(+)这个是将一个月的全遍历,就是即使表中没有2号的数据,也能有一条统计记录。如果没有这个需求的话,直接用下面的就行了: SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE, SUM(1) SS, COUNT(1) CC FROM T_AGENT_SERVICE_MSISDN WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04' GROUP BY SERVICE_OPENTIME嗯 谢谢 我参考下
按月统计,group by to_char(service_opentime,'yyyy-mm')
按周统计,group by to_char(service_opentiome,'IW')全年中的每一天,使用下面sql进行左关联
SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE
FROM (SELECT to_date(to_char(SYSDATE,'yyyy') || '-01-01','yyyy-mm-dd') SDATE,
to_date(to_char(SYSDATE,'yyyy') || '-12-31','yyyy-mm-dd') EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE;
FROM (SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE
FROM (SELECT TO_DATE('2014-04-01', 'yyyy-mm-dd') SDATE,
TO_DATE('2014-04-30', 'yyyy-mm-dd') EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) T1,
(SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE,
SUM(1) SS,
COUNT(1) CC
FROM T_AGENT_SERVICE_MSISDN
WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04'
GROUP BY SERVICE_OPENTIME) T2
WHERE T1.DDATE = T2.SDATE(+)这个是将一个月的全遍历,就是即使表中没有2号的数据,也能有一条统计记录。如果没有这个需求的话,直接用下面的就行了:
SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE,
SUM(1) SS,
COUNT(1) CC
FROM T_AGENT_SERVICE_MSISDN
WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04'
GROUP BY SERVICE_OPENTIME
FROM (SELECT TO_CHAR(SDATE + (ROWNUM - 1), 'yyyy-mm-dd') DDATE
FROM (SELECT TO_DATE('2014-04-01', 'yyyy-mm-dd') SDATE,
TO_DATE('2014-04-30', 'yyyy-mm-dd') EDATE
FROM DUAL) T
CONNECT BY SDATE + (ROWNUM - 1) <= EDATE) T1,
(SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE,
SUM(1) SS,
COUNT(1) CC
FROM T_AGENT_SERVICE_MSISDN
WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04'
GROUP BY SERVICE_OPENTIME) T2
WHERE T1.DDATE = T2.SDATE(+)这个是将一个月的全遍历,就是即使表中没有2号的数据,也能有一条统计记录。如果没有这个需求的话,直接用下面的就行了:
SELECT TO_CHAR(SERVICE_OPENTIME, 'yyyy-mm-dd') SDATE,
SUM(1) SS,
COUNT(1) CC
FROM T_AGENT_SERVICE_MSISDN
WHERE TO_CHAR = (SERVICE_OPENTIME, 'yyyy-mm') = '2014-04'
GROUP BY SERVICE_OPENTIME嗯 谢谢 我参考下
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '02', 1, 0)) M2,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '03', 1, 0)) M3,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '04', 1, 0)) M4,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '05', 1, 0)) M5,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '06', 1, 0)) M6,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '07', 1, 0)) M7,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '08', 1, 0)) M8,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '09', 1, 0)) M9,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '10', 1, 0)) M10,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '11', 1, 0)) M11,
SUM(DECODE(TO_CHAR(B.SERVICE_OPENTIME, 'MM'), '12', 1, 0)) M12
FROM T_AGENT_SERVICE_MSISDN B
WHERE 1 = 1
AND TO_CHAR(B.SERVICE_OPENTIME, 'yyyy') = '2014'