从每个小时获取三个随机日期
标题并未完全描述问题，即：我有一个带日期的表格 -
1. 2011-07-01 13:01:48
2. 2011-07-01 13:09:36
3. 2011-07-01 13:21:24
4. 2011-07-01 13:35:12
5. 2011-07-01 13:49:23
6. 2011-07-01 13:57:47
7. 2011-07-01 14:05:12
8. 2011-07-01 14:12:45
9. 2011-07-01 14:31:48
10. 2011-07-01 14:47:31
等等。 我需要的是获得每个小时的三个随机日期，例如：
1. 2011-07-01 13:01:48
2. 2011-07-01 13:21:24
3. 2011-07-01 13:49:23
4. 2011-07-01 14:05:12
5. 2011-07-01 14:12:45
6. 2011-07-01 14:47:31
我怎样才能在MySQL中做到这一点？
如果你每小时有足够的统计数据，这应该工作得很好：
select *
from (
select *
from yourtable
order by rand()
)
group by date(yourdate), hour(yourdate), floor(rand()*3)
create table dates (`date` datetime);
insert into dates (`date`) values
('2011-07-11 06:05:02'),
('2011-07-11 06:15:02'),
('2011-07-11 06:45:02'),
('2011-07-11 06:55:02'),
('2011-07-11 06:56:02'),
('2011-07-11 08:05:02'),
('2011-07-11 08:07:02'),
('2011-07-11 08:09:02'),
('2011-07-11 08:11:02'),
('2011-07-11 08:40:02'),
('2011-07-11 09:05:02'),
('2011-07-11 11:10:02'),
('2011-07-11 11:11:02'),
('2011-07-11 11:55:02')
;
set @i := 0;
set @d := '';
select `date`
from (
select
case
when @d != date_format(`date`, '%Y-%m-%d %H')
then @i := 0
else @i := @i + 1
end as i,
case
when @d != date_format(`date`, '%Y-%m-%d %H')
then @d := date_format(`date`, '%Y-%m-%d %H')
end as d,
case when @i < 3 then `date` else null end as `date`
from (
select `date`
from dates
order by date_format(`date`, '%Y-%m-%d %H'), rand()
) ss
) sw
where `date` is not null
order by `date`
;
如果数据集不是很大 - 如果数据集很大，那么无论如何您都不应该在数据集上使用一些随机算法，那么您可以使用以下简单查询：
select * from t order by rand() limit 6;