熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle中一個按月分組的方法

2013-11-13 22:11:20  來源: 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
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.