MySQL实战基础知识入门(2)：统计一天24小时数据默认补0的sql语句

最新推荐文章于 2024-05-01 22:54:01 发布

漏刻有时

最新推荐文章于 2024-05-01 22:54:01 发布

阅读量1.3k

点赞数 1

分类专栏： mysql

LOCKDATAV出品，为避免断章取义走弯路，请注意环境配置和时间。

本文链接：https://blog.csdn.net/weixin_41290949/article/details/107705641

版权

mysql 专栏收录该内容

30 篇文章 1 订阅

订阅专栏

语句分析：如果不存在某时段的数据，会显示为null，如何将不存在的时段自动补齐呢？

select a.lockdate,ifnull(b.count,0) as count
from (
   SELECT 0  as lockdate
    union all
    SELECT 1 as lockdate
    union all
    SELECT 2 as lockdate
    union all
    SELECT 3 as lockdate
    union all
    SELECT 4 as lockdate
    union all
    SELECT 5 as lockdate
    union all
    SELECT 6 as lockdate
		union all
    SELECT 7 as lockdate
		union all
    SELECT 8 as lockdate
		union all
    SELECT 9 as lockdate
		union all
    SELECT 10 as lockdate
		union all
    SELECT 11 as lockdate
		union all
    SELECT 12 as lockdate
		union all
    SELECT 13 as lockdate
		union all
    SELECT 14 as lockdate
		union all
    SELECT 15 as lockdate
		union all
    SELECT 16 as lockdate
		union all
    SELECT 17 as lockdate
		union all
    SELECT 18 as lockdate
		union all
    SELECT 19 as lockdate
		union all
    SELECT 20 as lockdate
		union all
    SELECT 21 as lockdate
		union all
    SELECT 22 as lockdate
		union all
    SELECT 23 as lockdate
) a left join (
	SELECT
		HOUR (login_time) AS hours
		FROM
			tb_log_login
		WHERE
			DATE_FORMAT(login_time, '%Y-%m-%d') = DATE_FORMAT('2020-07-31', '%Y-%m-%d')
		GROUP BY
		hours
) b on a.lockdate = b.hours;