MySQL按小时查询数据,没有的补0
作者:Princar 时间:2024-01-18 00:55:30
需求背景
一个统计接口,前端需要返回两个数组,一个是0-23的小时计数,一个是各小时对应的统计数。
思路 直接使用group by查询要统计的表,当某个小时统计数为0时,会没有该小时分组。思考了一下,需要建立辅助表,只有一列小时,再插入0-23共24个小时
CREATE TABLE hours_list (
hour int NOT NULL PRIMARY KEY
)
先查小时表,再做连接需要查的表,即可将没有统计数的小时填充上0。这里由于需要查多个表中,create_time在每个小时区间内、且SOURCE_ID等于查询条件的统计之和,所以UNION ALL了多张表
SELECT
t.HOUR,
sum(t.HOUR_COUNT) hourCount
FROM
(SELECT
hs. HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FROM
cbc_hours_list hs
LEFT JOIN cbc_source_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_hs 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_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
UNION ALL
SELECT
hs.HOUR AS HOUR,
COUNT(cs.RECORD_ID) AS HOUR_COUNT
FROM
cbc_hours_list 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 && 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_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的小时也可以查出来了。
来源:https://blog.csdn.net/Princar/article/details/111302667
标签:MySQL,查询数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
django中瀑布流写法实例代码
2022-08-04 11:11:26
![](https://img.aspxhome.com/file/2023/6/92366_0s.jpg)
openCV入门学习基础教程第三篇
2022-05-20 00:00:59
![](https://img.aspxhome.com/file/2023/4/68344_0s.jpg)
[译]Javascript风格要素(二)
2008-02-29 12:51:00
NumPy实现多维数组中的线性代数
2021-03-30 09:59:08
![](https://img.aspxhome.com/file/2023/8/89418_0s.png)
asp如何在聊天室实现趣味答题并计分功能?
2010-06-18 20:00:00
简单有效上手Python3异步asyncio问题
2022-01-14 02:28:34
![](https://img.aspxhome.com/file/2023/2/68422_0s.gif)
python绘制散点图并标记序号的方法
2023-12-22 09:59:49
![](https://img.aspxhome.com/file/2023/7/115877_0s.jpg)
Node.js使用NodeMailer发送邮件实例代码
2024-05-02 17:37:10
![](https://img.aspxhome.com/file/2023/9/129489_0s.png)
Perl5和Perl6对比使用Sigils的差别
2022-03-04 16:34:17
浅谈SQL Server中统计对于查询的影响分析
2012-06-06 20:08:23
iis7 ASP+Access数据库连接错误
2011-03-08 10:41:00
Python中使用urllib2模块编写爬虫的简单上手示例
2023-10-18 22:14:31
![](https://img.aspxhome.com/file/2023/4/92144_0s.jpg)
python中的property及属性与特性之间的优先权
2023-02-03 02:36:12
![](https://img.aspxhome.com/file/2023/7/90337_0s.png)
Python开发时报TypeError: ‘int‘ object is not iterable错误的解决方式
2023-08-23 20:30:05
Python lambda表达式原理及用法解析
2021-03-02 18:52:12
JavaScript实现五子棋游戏的方法详解
2024-04-30 10:11:54
![](https://img.aspxhome.com/file/2023/3/130493_0s.gif)
vue3 setup语法糖之组件传参(defineProps、defineEmits、defineExpose)示例详解
2024-04-27 16:01:39
用ASP木马实现FTP和解压缩
2008-02-13 08:47:00
python可视化plotly 图例(legend)设置
2021-05-17 11:17:56
![](https://img.aspxhome.com/file/2023/4/134474_0s.png)
运用PyTorch动手搭建一个共享单车预测器
2022-10-20 06:25:04
![](https://img.aspxhome.com/file/2023/0/95300_0s.png)