oracle查询一天内的数据_oracle查询一天中24小每个小时的数据统计

方案一：

SELECT count(*) total FROM UCP_MESSAGEREJECT um LEFT JOIN UCP_MESSAGECENTER uc ON UM.MSGID=UC.ID AND UC.SENDUSERDETCODE='20400' WHERE UC.SENDTIME between to_date('2019-06-19 17:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2019-06-19 17:59:50','yyyy-mm-dd hh24:mi:ss');

刚开始想到这么去操作想到程序处理与数据库交互比较多后想到能不能用一条SQL解决所以出现方案二

方案二：

with 
t as(select rownum-1 rn from dual connect by rownum<=24),
t1 as (SELECT to_char(UC.SENDTIME,'hh24') AS TT,count(*) TOTAL FROM UCP_MESSAGEREJECT UM LEFT JOIN UCP_MESSAGECENTER UC ON UM.MSGID=UC.ID AND UC.SENDUSERDETCODE='20400' 
WHERE to_char(UC.SENDTIME,'yyyy-mm-dd') LIKE '%2019-06-19%' GROUP BY to_char(UC.SENDTIME,'hh24'))
SELECT RN,NVL(TT,0) HOURS,NVL(TOTAL,0) TOTAL FROM t LEFT JOIN t1 ON t.RN=t1.TT ORDER BY RN ASC

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！