mysql - MySQL 查询中的三个小时时间间隔

Question

在 previous question 上可以创建一个表并用一个月中的几天填充，但我希望该表的填充略有不同:一个月中的每一天都应该有三个不同的小时间隔。

根据那个问题，这段代码由Tom Mac :

create table all_date 
(id int unsigned not null primary key auto_increment, 
a_date date not null,
last_modified timestamp not null default current_timestamp on update current_timestamp,
unique key `all_date_uidx1` (a_date));

然后，

DELIMITER //


CREATE PROCEDURE populate_all_dates(IN from_date DATE, IN days_into_future INT)

BEGIN

 DECLARE v_date DATE;
 DECLARE ix int;


 SET ix := 0;
 SET v_date := from_date;


 WHILE v_date <= (from_date + interval days_into_future day) DO

  insert into all_date (a_date) values (v_date) 
  on duplicate key update last_modified = now();

  set ix := ix +1;

  set v_date := from_date + interval ix day;

 END WHILE;

END//

DELIMITER ;

然后你可以运行:

call populate_all_dates('2011-10-01',30);

要填充 10 月的所有日期(或任何月份，更改函数的值)

有了它，我可以运行以下查询

select day(a.a_date) as 'October',
IFNULL(t.a1,0) as 'Auth1',
IFNULL(t.a2,0) as 'Auth2',
IFNULL(t.a50,0) as 'Auth50'
from all_date a
LEFT OUTER JOIN
(
SELECT date(wp.post_date) as post_date,
sum(case when wp.post_author = '1' then 1 else 0 end) as a1,
sum(case when wp.post_author = '2' then 1 else 0 end) as a2,
sum(case when wp.post_author = '50' then 1 else 0 end) as a50,
count(*) as 'All Auths'
FROM wp_posts wp
WHERE  wp.post_type = 'post'
AND wp.post_date  between '2011-10-01' and '2011-10-31 23:59:59'
GROUP BY date(wp.post_date)
) t
ON a.a_date = t.post_date
where a.a_date between '2011-10-01' and '2011-10-31'
group by day(a.a_date);

我会得到一个表格，其中包含按作者和日期划分的 WordPress 博客中的帖子数量，类似于:

+---------+---------+-------+------+---------+
| October | Auth1   | Auth2 | Auth3|  Auth4  |
+---------+---------+-------+------+---------+
|       1 |       0 |     0 |    0 |       0 |
|       2 |       0 |     0 |    1 |       0 |
|       3 |       4 |     4 |    6 |       2 |
|       4 |       4 |     3 |    5 |       2 |
|       5 |       7 |     0 |    5 |       2 |
|       6 |       4 |     4 |    0 |       2 |
|       7 |       0 |     2 |    1 |       2 |
|       8 |       0 |     0 |    7 |       0 |
.....
etc

但我想要的是每天分为三行，每一行对应以下时间范围:

00:00-14:3014:31-18:1518:16-23:59

所以表格应该显示类似的内容(例如，我不知道如何显示每个时间范围，所以一个好的方法应该是第 1 天，时间范围 1 (1-1) 等)。

+---------+---------+-------+------+---------+
| October | Auth1   | Auth2 | Auth3|  Auth4  |
+---------+---------+-------+------+---------+
|    1-1  |       0 |     0 |    0 |       0 |
|    1-2  |       0 |     0 |    0 |       0 |
|    1-3  |       0 |     0 |    0 |       0 |
|    2-1  |       0 |     0 |    1 |       0 |
|    2-2  |       0 |     0 |    0 |       0 |
|    2-3  |       0 |     0 |    0 |       0 |
|    3-1  |       1 |     2 |    3 |       0 |
|    3-2  |       1 |     2 |    2 |       2 |
|    3-3  |       2 |     0 |    1 |       0 |
etc...

如您所见，这三行的总和等于当天前一个唯一行中的每一行。

这可能吗？

Answer 1

使用(更新#2)

SELECT 
a.a_datetm as 'October',
IFNULL(p.a1,0) as 'Auth1',
IFNULL(p.a2,0) as 'Auth2',
IFNULL(p.a50,0) as 'Auth50'
FROM
(
SELECT CONCAT (day(X.a_date), '-1') AS a_datetm
FROM all_date X
WHERE X.a_date between '2011-10-01' and '2011-10-31'
UNION ALL
SELECT CONCAT (day(Y.a_date), '-2') AS a_datetm
FROM all_date Y
WHERE Y.a_date between '2011-10-01' and '2011-10-31'
UNION ALL
SELECT CONCAT (day(Z.a_date), '-3') AS a_datetm
FROM all_date Z
WHERE Z.a_date between '2011-10-01' and '2011-10-31'
) a
LEFT OUTER JOIN
(
SELECT 
CONCAT (day(wp.post_date), (CASE WHEN (TIME(wp.post_date) < '14:31:00') THEN '-1' WHEN (TIME(wp.post_date) BETWEEN '14:31:00' AND '18:15:59') THEN '-2' ELSE '-3' END )) AS a_datetm,
sum(case when wp.post_author = '1' then 1 else 0 end) as a1,
sum(case when wp.post_author = '2' then 1 else 0 end) as a2,
sum(case when wp.post_author = '50' then 1 else 0 end) as a50,
count(*) as 'All Auths'
FROM wp_posts wp
WHERE  wp.post_type = 'post'
AND wp.post_date  between '2011-10-01' and '2011-10-31 23:59:59'
GROUP BY CONCAT (day(wp.post_date), (CASE WHEN (TIME(wp.post_date) < '14:31:00') THEN '-1' WHEN (TIME(wp.post_date) BETWEEN '14:31:00' AND '18:15:59') THEN '-2' ELSE '-3' END ))
) p
ON a.a_datetm = p.a_datetm
ORDER BY a.a_datetm ASC;