時間：2015/6/28來源：IT貓撲網(wǎng)作者：網(wǎng)管聯(lián)盟我要評論(0)
最近項目中要出一個按月、周、日統(tǒng)計的報表
某年按月分組：
Sql代碼
SELECT A.D as label,NVL(B.NUM,0) AS value
FROM
(
SELECT ROWNUM AS D FROM SYS_UNUSE WHERE ROWNUM < 13
) A
LEFT JOIN
(
SELECT TO_CHAR(T.STARTTIME,'MM') AS D,COUNT(1) AS NUM FROM CALL_HIS T
WHERE T.STARTTIME>=:prStartDate AND T.STARTTIME<:prEndDate
GROUP BY TO_CHAR(T.STARTTIME,'MM')
) B ON A.D=B.D ORDER BY A.D
某月按日分組：
Sql代碼
SELECT A.D as label,NVL(B.NUM,0) AS value
FROM
(
SELECT ROWNUM AS D FROM SYS_UNUSE WHERE ROWNUM <= TO_CHAR(LAST_DAY(:prDate),'DD')
) A
LEFT JOIN
(
SELECT TO_CHAR(T.STARTTIME,'MM') AS D,COUNT(1) AS NUM FROM CALL_HIS T
WHERE T.STARTTIME>=:prStartDate AND T.STARTTIME<:prEndDate
GROUP BY TO_CHAR(T.STARTTIME,'MM')
) B ON A.D=B.D ORDER BY A.D
按周統(tǒng)計：
Sql代碼
SELECT A.D as label,NVL(B.NUM,0) AS value
FROM
(
SELECT TO_CHAR(:prDate+ROWNUM-1,'yyyy-MM-DD') as D FROM SYS_UNUSE WHERE ROWNUM <= 7
) A
LEFT JOIN
(
SELECT TO_CHAR(T.STARTTIME,'yyyy-MM-DD') AS D,COUNT(1) AS NUM FROM CALL_HIS T
WHERE T.STARTTIME>=:prStartDate AND T.STARTTIME<:prEndDate
GROUP BY TO_CHAR(T.STARTTIME,'yyyy-MM-DD')
) B ON A.D=B.D ORDER BY A.D
說明：SYS_UNUSE表是一個沒用的表，跟業(yè)務(wù)沒有任何關(guān)系，表里只有100條數(shù)據(jù)，我這里是足夠用了。
關(guān)鍵詞標(biāo)簽：oracle
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列
oracle中使用SQL語句修改字段類型-oracle修
使用低權(quán)限Oracle數(shù)據(jù)庫賬戶得到管理員權(quán)限
Oracle對user的訪問控制
人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語句Oracle中使用alter table來增加,刪除,修改列的語法ORACLE和SQL語法區(qū)別歸納(1)oracle grant 授權(quán)語句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限