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
           

分步解析：

1、用with建立兩個臨時表t、t1

t表用connect by 查出24行資料對應一天當中的24小時（connect by不懂幹啥的可去谷哥、百度、搜狗去查找，此處不作詳叙）

t1表用分組查詢出一天中資料的統計:

按小時分組查詢統計SQL

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');--按小時分組
           

2、兩個表聯合查詢

SELECT RN,NVL(TT,0) HOURS,NVL(TOTAL,0) TOTAL FROM t LEFT JOIN t1 ON t.RN=t1.TT ORDER BY RN ASC
           

nvl用法是判斷預設值如果為null則取0

最後：

統計月份、年份也可參考此篇用法相同