我想为我的一个图表检索数据，我想为它想出一个简单的解决方案，而不是做8个子查询。
这就是users表的外观：
user_id
1
2
3
4
5
这就是
pts表的外观：id user_id status points time
1 1 0 100 2014-08-23 03:34:54
2 4 0 100 2014-08-23 04:04:44
3 1 1 300 2014-08-23 08:34:21
4 1 0 300 2014-08-23 15:25:11
5 1 0 200 2014-08-23 22:23:12
查询看起来像这样（有一些注释，我不知道该放什么）：
"SELECT *,
(
SELECT SUM(points)
FROM pts
WHERE status = 0
AND user_id = 1
AND time >= "THIS DAY'S 00:00" AND <= "THIS DAY'S 03:00"
),
(
SELECT SUM(points)
FROM pts
WHERE status = 0
AND user_id = 1
AND time >= "THIS DAY'S 03:00" AND <= "THIS DAY'S 06:00"
),
(
SELECT SUM(points)
FROM pts
WHERE status = 0
AND user_id = 1
AND time >= "THIS DAY'S 06:00" AND <= "THIS DAY'S 09:00"
),
(
SELECT SUM(points)
FROM pts
WHERE status = 0
AND user_id = 1
AND time >= "THIS DAY'S 12:00" AND <= "THIS DAY'S 15:00"
),
"ETC......"
FROM pts
WHERE user_id = 1
AND status = 1
"
所以基本上，查询需要在当前日期每3小时从数据库中收集数据（如果我使用上面的选项，那么将是8个子查询）。如果还未达到间隔的当前时间，则查询应返回0/null。如果当前时间在某个间隔内，则应返回从间隔开始到间隔结束的数据。
我想达到的结果是：
id user_id points time
1 1 0 2014-08-23 00:00:00 - 2014-08-23 03:00:00
2 1 200 2014-08-23 03:00:00 - 2014-08-23 06:00:00
3 1 300 2014-08-23 06:00:00 - 2014-08-23 09:00:00
4 1 0 2014-08-23 09:00:00 - 2014-08-23 12:00:00
5 1 0 2014-08-23 12:00:00 - 2014-08-23 15:00:00
6 1 300 2014-08-23 15:00:00 - 2014-08-23 18:00:00
7 1 0 2014-08-23 18:00:00 - 2014-08-23 21:00:00
8 1 200 2014-08-23 21:00:00 - 2014-08-23 00:00:00
这在MySQL中可能吗？
最佳答案
您可以在小时内使用聚合和算术：
select floor(hour(time) / 3) as hourgroup, sum(points)
from pts
where status = 0 and user_id = 1 and
date(time) = "THIS DAY"
group by floor(hour(time) / 3);
对于您的特定输出：
select (hour(time) div 3) as id, user_id, sum(points),
date('2014-08-23') + interval 3 * (hour(time) div 3) hour as starttime,
date('2014-08-23') + interval 3 * ((hour(time) div 3) + 1) hour as endtime
from pts
where status = 0 and user_id = 1 and
date(time) = date('2014-08-23')
group by floor(hour(time) / 3);
计数为零的值有问题。我不知道这些有多重要。查询将数据进行透视，但所需的结果未进行透视。
编辑：
如果你真的需要所有小组的时间，你可以：
select n.n as id, user_id, sum(points),
date('2014-08-23') + interval 3 * n.n hour as starttime,
date('2014-08-23') + interval 3 * (n.n + 1) hour as endtime
from (select 0 as n union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7
) n left join
pts
on n.n = hour(time) div 3
where status = 0 and user_id = 1 and
date(time) = date('2014-08-23')
group by n.n;