Mysql - 在同一个查询中获取不同时间间隔的数据

Question

我的下面的查询给了我过去2小时播放的顶级视频的结果，但是我的要求是也获得过去4小时的顶级视频到过去2小时，例如如果通过这个查询，我从01:00 PM到03:00 PM获取数据，我还希望从09:00 AM到01:00 PM的数据。我可以在一个查询中以高效的方式做到这一点。

查询：

select SQL_CACHE channel,SUBSTRING_INDEX(GROUP_CONCAT(video_id ORDER BY plays DESC),',', 40) AS video_ids,now() as datetime from 
(SELECT channel,video_id,count(video_id) as plays FROM `tbl` 
WHERE `datetime_col` > DATE_SUB(now(), INTERVAL 2 HOUR) 
and channel != 0 
and cat_id != 8 
group by channel,video_id 
order by channel,plays DESC)x 
group by channel; 

在此先感谢。

Answer 1

您可以使用与UNION相同的查询对结果进行分组。此外，您可以使用BETWEEN来定义间隔，例如

select SQL_CACHE channel,SUBSTRING_INDEX(GROUP_CONCAT(video_id ORDER BY plays DESC),',', 40) AS video_ids,now() as datetime from 
(SELECT channel,video_id,count(video_id) as plays FROM `tbl` 
WHERE `datetime_col` BETWEEN DATE_SUB(now(), INTERVAL 2 HOUR) AND NOW() 
and channel != 0 
and cat_id != 8 
group by channel,video_id 
order by channel,plays DESC)x 
group by channel 

UNION 

select SQL_CACHE channel,SUBSTRING_INDEX(GROUP_CONCAT(video_id ORDER BY plays DESC),',', 40) AS video_ids,DATE_SUB(now(), INTERVAL 2 HOUR) as datetime from 
(SELECT channel,video_id,count(video_id) as plays FROM `tbl` 
WHERE `datetime_col` BETWEEN DATE_SUB(now(), INTERVAL 4 HOUR) AND DATE_SUB(now(), INTERVAL 2 HOUR) 
and channel != 0 
and cat_id != 8 
group by channel,video_id 
order by channel,plays DESC)x 
group by channel;