按组获取前n个结果

Question

我正在使用SQL从表中检索最近的20行，按日期分组。 我希望将其限制在每个post_day组内，仅选择前10行投票 DESC。

SELECT *, DATE(timestamp) as post_day 
FROM stories 
ORDER BY post_day DESC, votes DESC
LIMIT 0, 20

以下是表格的样式：

STORYID         TIMESTAMP           VOTES
1               2015-03-10          1
2               2015-03-10          2
3               2015-03-9           5
4               2015-03-9           3

- user1829122

---

展示一些虚拟数据以演示您确切的需求。 - Ameya Deshpande

Answer 1

架构

create table stories
(   storyid int auto_increment primary key,
    theDate date not null,
    votes int not null
);

insert stories(theDate,votes) values 
('2015-03-10',1),
('2015-03-10',2),
('2015-03-09',5),
('2015-03-09',3),
('2015-03-10',51),
('2015-03-10',26),
('2015-03-09',75),
('2015-03-09',2),
('2015-03-10',12),
('2015-03-10',32),
('2015-03-09',51),
('2015-03-09',63),
('2015-03-10',1),
('2015-03-10',11),
('2015-03-09',5),
('2015-03-09',21),
('2015-03-10',1),
('2015-03-10',2),
('2015-03-09',5),
('2015-03-09',3),
('2015-03-10',51),
('2015-03-10',26),
('2015-03-09',75),
('2015-03-09',2),
('2015-03-10',12),
('2015-03-10',44),
('2015-03-09',11),
('2015-03-09',7),
('2015-03-10',19),
('2015-03-10',7),
('2015-03-09',51),
('2015-03-09',79);

查询

set @rn := 0, @thedate := '';
select theDate, votes
from 
(
   select storyid, theDate, votes,
      @rn := if(@thedate = theDate, @rn + 1, 1) as rownum,
      @thedate := theDate as not_used
  from stories
  order by theDate, votes desc
) A
where A.rownum <= 10;

结果

+------------+-------+
| theDate    | votes |
+------------+-------+
| 2015-03-09 |    79 |
| 2015-03-09 |    75 |
| 2015-03-09 |    75 |
| 2015-03-09 |    63 |
| 2015-03-09 |    51 |
| 2015-03-09 |    51 |
| 2015-03-09 |    21 |
| 2015-03-09 |    11 |
| 2015-03-09 |     7 |
| 2015-03-09 |     5 |
| 2015-03-10 |    51 |
| 2015-03-10 |    51 |
| 2015-03-10 |    44 |
| 2015-03-10 |    32 |
| 2015-03-10 |    26 |
| 2015-03-10 |    26 |
| 2015-03-10 |    19 |
| 2015-03-10 |    12 |
| 2015-03-10 |    12 |
| 2015-03-10 |    11 |
+------------+-------+
20 rows in set, 1 warning (0.00 sec)

- Drew

Answer 2

通常，您应该在每个组内使用ROW_NUMBER()来排序记录，然后选择ROW_NUMBER <= 10的记录。在MySQL中没有ROW_NUMBER()聚合函数，但是您可以在MySQL中使用用户定义变量来模拟ROW_NUMBER()。

select storyId, post_day , votes
from (
   select storyId,
          DATE(timestamp) as post_day, 
          votes,
          @num := if(@grp = DATE(timestamp), @num + 1, 1) as row_number,
          @grp := DATE(timestamp) as dummy
  from stories,(select @num := 0, @grp := null) as T
  order by DATE(timestamp) DESC, votes DESC
) as x where x.row_number <= 10;

SQLFiddle演示

还可以看看： 如何在SQL中选择每个组的第一个/最小/最大行

- valex