時(shí)間:2015/6/28來源:IT貓撲網(wǎng)作者:網(wǎng)管聯(lián)盟我要評(píng)論(0)
最近項(xiàng)目中要出一個(gè)按月、周、日統(tǒng)計(jì)的報(bào)表
某年按月分組:
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)計(jì):
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表是一個(gè)沒用的表,跟業(yè)務(wù)沒有任何關(guān)系,表里只有100條數(shù)據(jù),我這里是足夠用了。
關(guān)鍵詞標(biāo)簽:oracle
相關(guān)閱讀
熱門文章 Oracle中使用alter table來增加,刪除,修改列oracle中使用SQL語(yǔ)句修改字段類型-oracle修使用低權(quán)限Oracle數(shù)據(jù)庫(kù)賬戶得到管理員權(quán)限Oracle對(duì)user的訪問控制
人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語(yǔ)句Oracle中使用alter table來增加,刪除,修改列的語(yǔ)法ORACLE和SQL語(yǔ)法區(qū)別歸納(1)oracle grant 授權(quán)語(yǔ)句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限