按小时分组列，按标题分组行

owfi6suc 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(350)

这是一张tablehttps://paiza.io/projects/q1vykbxszklxqmiyqknwfw?language=mysql

title|record_datetime
--------------------------
fgame 2018-07-09 11:41:41
fgame 2018-07-09 11:41:25
fgame 2018-07-09 12:41:41
source 2018-07-09 10:40:18
source 2018-07-09 10:40:33
source 2018-07-09 10:41:10
setup 2018-07-24 09:49:11
setup 2018-07-24 09:49:13

我想要这个样品的结果

15列（标题+13小时+总和）和23行（22种不同标题+总和）
8到20小时按记录日期时间分组
我该怎么做才能做到这一点？我试过了

SELECT * FROM (SELECT title, count(*)as h8 FROM click_log where HOUR(record_datetime) = 8 GROUP BY title) as t8
Right join (SELECT title, count(*)as h9 FROM click_log where HOUR(record_datetime) = 9 GROUP BY title) as t9 on t8.title = t9.title
Right join (SELECT title, count(*)as h10 FROM click_log where HOUR(record_datetime) = 10 GROUP BY title) as t10 on t9.title = t10.title
Right join (SELECT title, count(*)as h11 FROM click_log where HOUR(record_datetime) = 11 GROUP BY title) as t11 on t10.title = t11.title
Right join (SELECT title, count(*)as h12 FROM click_log where HOUR(record_datetime) = 12 GROUP BY title) as t12 on t11.title = t12.title
Right join (SELECT title, count(*)as h13 FROM click_log where HOUR(record_datetime) = 13 GROUP BY title) as t13 on t12.title = t13.title
Right join (SELECT title, count(*)as h14 FROM click_log where HOUR(record_datetime) = 14 GROUP BY title) as t14 on t13.title = t14.title
Right join (SELECT title, count(*)as h15 FROM click_log where HOUR(record_datetime) = 15 GROUP BY title) as t15 on t14.title = t15.title
Right join (SELECT title, count(*)as h16 FROM click_log where HOUR(record_datetime) = 16 GROUP BY title) as t16 on t15.title = t16.title
Right join (SELECT title, count(*)as h17 FROM click_log where HOUR(record_datetime) = 17 GROUP BY title) as t17 on t16.title = t17.title
Right join (SELECT title, count(*)as h18 FROM click_log where HOUR(record_datetime) = 18 GROUP BY title) as t18 on t17.title = t18.title
Right join (SELECT title, count(*)as h19 FROM click_log where HOUR(record_datetime) = 19 GROUP BY title) as t19 on t18.title = t19.title
Right join (SELECT title, count(*)as h20 FROM click_log where HOUR(record_datetime) = 20 GROUP BY title) as t20 on t19.title = t20.title
Right join (SELECT title, count(*)as allh FROM click_log where 1=1 GROUP BY title) as tallh on t20.title = tallh.title
UNION 
SELECT * FROM (SELECT title, count(*)as h8 FROM click_log where HOUR(record_datetime) = 8 GROUP BY title) as t8
LEFT join (SELECT title, count(*)as h9 FROM click_log where HOUR(record_datetime) = 9 GROUP BY title) as t9 on t8.title = t9.title
LEFT join (SELECT title, count(*)as h10 FROM click_log where HOUR(record_datetime) = 10 GROUP BY title) as t10 on t9.title = t10.title
LEFT join (SELECT title, count(*)as h11 FROM click_log where HOUR(record_datetime) = 11 GROUP BY title) as t11 on t10.title = t11.title
LEFT join (SELECT title, count(*)as h12 FROM click_log where HOUR(record_datetime) = 12 GROUP BY title) as t12 on t11.title = t12.title
LEFT join (SELECT title, count(*)as h13 FROM click_log where HOUR(record_datetime) = 13 GROUP BY title) as t13 on t12.title = t13.title
LEFT join (SELECT title, count(*)as h14 FROM click_log where HOUR(record_datetime) = 14 GROUP BY title) as t14 on t13.title = t14.title
LEFT join (SELECT title, count(*)as h15 FROM click_log where HOUR(record_datetime) = 15 GROUP BY title) as t15 on t14.title = t15.title
LEFT join (SELECT title, count(*)as h16 FROM click_log where HOUR(record_datetime) = 16 GROUP BY title) as t16 on t15.title = t16.title
LEFT join (SELECT title, count(*)as h17 FROM click_log where HOUR(record_datetime) = 17 GROUP BY title) as t17 on t16.title = t17.title
LEFT join (SELECT title, count(*)as h18 FROM click_log where HOUR(record_datetime) = 18 GROUP BY title) as t18 on t17.title = t18.title
LEFT join (SELECT title, count(*)as h19 FROM click_log where HOUR(record_datetime) = 19 GROUP BY title) as t19 on t18.title = t19.title
LEFT join (SELECT title, count(*)as h20 FROM click_log where HOUR(record_datetime) = 20 GROUP BY title) as t20 on t19.title = t20.title
LEFT join (SELECT title, count(*)as allh FROM click_log where 1=1 GROUP BY title) as tallh on t20.title = tallh.title

sql mariadb pivot-table

来源：https://stackoverflow.com/questions/62511903/mariadb-column-group-by-hour-row-group-by-title

2条答案

按热度按时间

7ajki6be1#

只是使用 with rollup 和条件聚合：

SELECT COALESCE(title, 'sum'),
       SUM( HOUR(record_datetime) = 8 ) AS hour_08,
       SUM( HOUR(record_datetime) = 9 ) AS hour_09,
       SUM( HOUR(record_datetime) = 10 ) AS hour_10,
       . . .,
       COUNT(*) as total
FROM your_table
GROUP BY title WITH ROLLUP;

注意，我给出了不需要转义的列名。你可以随意使用这样的名字 08:00 ，但我推荐不需要转义的名称。

赞(0）回复(0）举报 2021-07-24

2sbarzqh2#

如果我正确理解您的需求，下面的查询将帮助您获得所需的输出-
此处演示

SELECT title,
SUM(CASE WHEN HOUR(record_datetime) = 6 THEN 1 ELSE 0 END) AS '06.00',
SUM(CASE WHEN HOUR(record_datetime) = 7 THEN 1 ELSE 0 END) AS '07.00',
SUM(CASE WHEN HOUR(record_datetime) = 8 THEN 1 ELSE 0 END) AS '08.00',
SUM(1) total
FROM your_table
GROUP BY title

UNION ALL

SELECT 'sum', 
SUM(CASE WHEN HOUR(record_datetime) = 6 THEN 1 ELSE 0 END) AS '06.00',
SUM(CASE WHEN HOUR(record_datetime) = 7 THEN 1 ELSE 0 END) AS '07.00',
SUM(CASE WHEN HOUR(record_datetime) = 8 THEN 1 ELSE 0 END) AS '08.00',
SUM(1) total
FROM your_table

我刚加了6、7和8小时作为样本。您可以根据需要在查询中添加其他时间。
第二个查询的输出也可以在应用程序的前端生成。但如果这是通过查询生成此部分的要求，则可以使用上述给定的逻辑。

赞(0）回复(0）举报 2021-07-24

我来回答

按小时分组列，按标题分组行

2条答案

相关问题

热门标签

最新问答