oracle中一個按月分組的方法

　　最近項目中要出一個按月周日統計的報表

　　某年按月分組
    Sql代碼
    SELECT AD as labelNVL(BNUM) AS value
           FROM
            (
              SELECT ROWNUM AS D FROM SYS_UNUSE WHERE ROWNUM <
            )  A
            LEFT JOIN
            (
                 SELECT TO_CHAR(TSTARTTIMEMM) AS DCOUNT() AS NUM FROM CALL_HIS T
                 WHERE TSTARTTIME>=:prStartDate AND TSTARTTIME<:prEndDate
                 GROUP BY TO_CHAR(TSTARTTIMEMM)
            ) B ON AD=BD ORDER BY AD

　　某月按日分組
    Sql代碼
    SELECT AD as labelNVL(BNUM) AS value
           FROM
            (
              SELECT ROWNUM AS D FROM SYS_UNUSE WHERE ROWNUM <= TO_CHAR(LAST_DAY(:prDate)DD)
            )  A
            LEFT JOIN
            (
                 SELECT TO_CHAR(TSTARTTIMEMM) AS DCOUNT() AS NUM FROM CALL_HIS T
                 WHERE TSTARTTIME>=:prStartDate AND TSTARTTIME<:prEndDate
                 GROUP BY TO_CHAR(TSTARTTIMEMM)
            ) B ON AD=BD ORDER BY AD

　　按周統計
    Sql代碼
    SELECT AD as labelNVL(BNUM) AS value
           FROM
            (
              SELECT TO_CHAR(:prDate+ROWNUMyyyyMMDD) as D FROM SYS_UNUSE WHERE ROWNUM <=
            )  A
            LEFT JOIN
            (
                 SELECT TO_CHAR(TSTARTTIMEyyyyMMDD) AS DCOUNT() AS NUM FROM CALL_HIS T
                 WHERE TSTARTTIME>=:prStartDate AND TSTARTTIME<:prEndDate
                 GROUP BY TO_CHAR(TSTARTTIMEyyyyMMDD)
            ) B ON AD=BD ORDER BY AD

　　說明SYS_UNUSE表是一個沒用的表跟業務沒有任何關系表裡只有條數據我這裡是足夠用了

From:http://tw.wingwit.com/Article/program/Oracle/201311/18420.html