亚洲女同成aV人片在线观看|亚洲www啪成人一区二区麻豆|亚洲国产中日韩精品综合|亚洲国产成人精品一级片|亚洲无码在线视频免费

這篇文章主要介紹了MySQL按小時(shí)查詢(xún)數據,查詢(xún)沒(méi)有的補0,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細，對大家的數據學(xué)習或工作具有一定的參考借鑒價(jià)值，需要的查詢(xún)朋友可以參考下ヽ(′?｀)ノ

需求背景

一個(gè)??統計接口，前端需要返回兩個(gè)數組，數據一個(gè)是查詢(xún)0-23的小時(shí)計數，一個(gè)是數據各小時(shí)對應的統計數。

思路 直接使用group by查詢(xún)要統計的查詢(xún)表，當某個(gè)小時(shí)統計數為0時(shí)，數據會(huì )沒(méi)有該小時(shí)分組。查詢(xún)思考了一下，數據需要建立輔助表，查詢(xún)只有一列小時(shí)，數據再插入0-23共24個(gè)小時(shí)

CREATE TAB(′ω｀*)LE hours_list (
hour int NOT NULL PRIMARY KEY
)

先查小時(shí)表，查詢(xún)再做連接需要查的數據表，即可將沒(méi)有統計數的查詢(xún)小時(shí)填充上0。這里由于需要查多(duo)個(gè)表中，create_time在每個(gè)小時(shí)(shi)區間內、??且SOURCE_ID等于查詢(xún)條件的統(tong)計之和，所以UNION ALヾ(′▽?zhuān)??L了多張表

SELECT
t.HOUR,
sum(t.HOUR_COUNT) hourCount
FROM
(SELECT
hs. HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FRヽ(′ー｀)ノOM
cbc_hours_l(′ω｀*)ist hs
LEFT JOIN cbc_source_0002 cs ON HOUR (cs.create_time) = hs. HOUR
AND cs.create_time > #{ startTime}
AND cs.creat(′?ω?`)e_time <= #{ endTime}
<#if sourceId?exists && sourceId !=''>
AND SOURCE_ID = #{ sourceId}
</#(??ヮ?)?*:???if>
GRO??UP BY
hs. HOUR
UNION ALL
SELECT
hs.HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FROM
cbc_hours_list hs
LEFT JOIN cbc_source_hs cs ON HOUR (cs.create_time) = hs. HOUR
AND cs.create_time > #{ startTime}
AND cs.create_time <= #{ endTime}
<#if sourceI??d?exists &&??amp; sourceId !=''>??;
AND SOURCE_ID = #{ sourceId}
</#if>
GROUP BY
hs. HOUR
UNIヽ(′▽?zhuān)?ノON ALL
SELECT
hs.HOUR AS HO(°ロ°) !UR,
COUNT(cs.RECORD_ID) AS HOUR_CO??(′▽?zhuān)?)UNT
FROM
cbc_h???ours_list hs
LEFT JOIN cbc_source_kfyj cs ON HOUR (cs.create_time) = hs. HOUR
AN??D cs.create_time > #{ startTi(T_T)me}
AND cs.create_time <= #{ endTime}
<#if sourceId?exists && sourceId !=??''>
AND SOURCE_ID = #{ sourceId}
</#if>
GROUP BY
hs. HOUR
UNION ALL
SELECT
hs.HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_CO??UNT
FROM
cbc_hours_li(′▽?zhuān)?)st hs
LEFT JOIN cbc_source_his_0002 cs ON HOUR (cs.create_(╯°□°）╯︵ ┻━┻time) = hs. HOUR
AND cs.create_time > #{ startTime}
AND cs.create_time <??;= #{ endTime}
<#if sourceId?exists && sourceId !=''>
AND SOURCE_ID(????) = #{ sourceId}
</#if>??;(′?｀)
GROUP BY
hs. HOUR
UNION ALL
SELECT
hs.HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FROM
cbc_hours_list hs
LEFT JOIN cbc_source_his_hs cs ON HOUR (cs.create_time) = hs. HOUR
AND cs.create_time > #{ startTime}
AND?? cs.create_time <= #{ endTime}
<#if sourceId?exists && sourceI(′；д；`)d !=''>
AND SOURCE_ID = #{ sourc(′ω｀*)eId}
&l??t;/#if>
GROUP BY
hs. HOUR
UNION ALL
SELECT
hs.HOUR?? AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FROM
cbc_hours_list hs
LEFT JOIN cbc_source_his_kfyj cs ON HOUR (cs.create_time) = hs. HOUR
AND cs.create_time > #{ startTime}
AND cs.create_time <= #{ endTime}
<#if sourceId?exists && sourceId !=''>
AND SOURCE_ID = #{ sourceId}
</#if>
GROUP BY
hs. HOUR) t
GROUP BY
t.hour

效果

統計數為0的小時(shí)也可以查出來(lái)了。

來(lái)源：腳本之家

鏈接：https://www.jb51.net/article/202439.h??tm