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

语句分析:如果不存在某时段的数据,会显示为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;

Done!

©️2020 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值