mysql 查询结果分段整合 有什么更好的方法嘛？

想把时间分成4段，并统计每段时间的总人数，达成以下效果

以下是我写的代码，感觉有点乱，有没有什么更好的办法改进一下呀？

WITH t1 AS (
    # 分段整合
    SELECT
        SUM( IF ( log_hour IN ( 0, 1, 2, 3, 4, 5 ), num_user, NULL ) ) AS '0to6',
        SUM( IF ( log_hour IN ( 6, 7, 8, 9, 10, 11 ), num_user, NULL ) ) AS '6to12',
        SUM( IF ( log_hour IN ( 12, 13, 14, 15, 16, 17 ), num_user, NULL ) ) AS '12to18',
        SUM( IF ( log_hour IN ( 18, 19, 20, 21, 22, 23 ), num_user, NULL ) ) AS '18to24' 
    FROM
        ( 
        # 提取hour
        SELECT 
            HOUR ( log_hms ) AS log_hour,
            COUNT( user_id ) AS num_user 
            FROM 
                login_rec 
            GROUP BY 
                log_hour ) a 
    ) 
# 列转行
SELECT
    '0to6' AS period,
    MAX( 0to6 ) AS num 
FROM
    t1 
UNION
SELECT
    '6to12' AS period,
    MAX( 6to12 ) AS num 
FROM
    t1 
UNION
SELECT
    '12to18' AS period,
    MAX( 12to18 ) AS num 
FROM
    t1 
UNION
SELECT
    '18to24' AS period,
    MAX( 18to24 ) AS num 
FROM
    t1;

 找到整洁些的方法啦！n_n

# 先划分等级，再分组统计
DROP VIEW IF EXISTS u_1_3;
CREATE VIEW u_1_3 AS SELECT
(
	CASE
			WHEN login_hour IN ( 0, 1, 2 ) THEN '00:00-03:00' 
			WHEN login_hour IN ( 3, 4, 5 ) THEN '03:00-06:00' 
			WHEN login_hour IN ( 6, 7, 8 ) THEN '06:00-09:00' 
			WHEN login_hour IN ( 9, 10, 11 ) THEN '09:00-12:00' 
			WHEN login_hour IN ( 12, 13, 14 ) THEN '12:00-15:00' 
			WHEN login_hour IN ( 15, 16, 17 ) THEN '15:00-18:00' 
			WHEN login_hour IN ( 18, 19, 20 ) THEN '18:00-21:00' 
			ELSE '21:00-24:00' 
	END 
		) AS t_level,
		SUM( num_user ) AS num 
	FROM
		( SELECT 
				# 提取hour
				HOUR ( login_time ) AS login_hour, 
				COUNT( user_id ) AS num_user 
			FROM 
				re_login 
			GROUP BY 
				login_hour 
			ORDER BY 
				login_hour ) t 
	GROUP BY
		t_level;

 

如果只是要分别统计，完全可以用数学方法，判断时间整除6的得数
但是如果你后续还要行转列，还是需要给每个结果取一个名字
只要涉及到行转列，都简单不到哪去

select count() zs from login_rec where log_hms in(0, 1, 2, 3, 4, 5)
union all select count() zs from login_rec where log_hms in(6, 7, 8, 9, 10, 11)