mysql - 从每小时获取三个随机日期

Question

标题实际上并没有完全描述问题，即:我有一个日期表 -

 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中实现？

Answer 1

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`
;