最近项目中要出一个按月周日统计的报表
某年按月分组
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表是一个没用的表跟业务没有任何关系表里只有条数据我这里是足够用了
