在GROUP BY中使用LIMIT获取每个组的N个结果？

Question

以下查询：

SELECT
year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
AND id IN (SELECT rid FROM table2)
GROUP BY id, year
ORDER BY id, rate DESC

产生：

year    id  rate
2006    p01 8
2003    p01 7.4
2008    p01 6.8
2001    p01 5.9
2007    p01 5.3
2009    p01 4.4
2002    p01 3.9
2004    p01 3.5
2005    p01 2.1
2000    p01 0.8
2001    p02 12.5
2004    p02 12.4
2002    p02 12.2
2003    p02 10.3
2000    p02 8.7
2006    p02 4.6
2007    p02 3.3

我想要的只是每个id的前5个结果:

2006    p01 8
2003    p01 7.4
2008    p01 6.8
2001    p01 5.9
2007    p01 5.3
2001    p02 12.5
2004    p02 12.4
2002    p02 12.2
2003    p02 10.3
2000    p02 8.7

有没有一种类似于LIMIT的修饰符可以在GROUP BY内使用？

- Wells

---

这可以在MySQL中完成，但并不像添加“LIMIT”子句那么简单。下面是一篇详细解释该问题的文章：如何在SQL中选择每组的第一个/最小/最大行 这是一篇好文章 - 作者介绍了一个优雅而朴素的解决“每组前N个”问题的方法，然后逐步改进了它。 - danben

SELECT * FROM (SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC) LIMIT 5 - Mixcoatl

这个问题已经通过引入SQL窗口函数来解决，具体解释可以参考这个答案：https://dev59.com/B2sz5IYBdhLWcg3wpZvy#38854846 - Paramvir Singh Karwal

@danben 新链接 - syahid246

Answer 1

您想要找到每个组的前n行。本答案提供了一种通用解决方案，使用与OP不同的示例数据。

在MySQL 8或更高版本中，您可以根据前5个的确切定义使用ROW_NUMBER、RANK或DENSE_RANK函数。以下是这些函数生成的数字，基于按value降序排序。请注意如何处理平局：

pkid	catid	value	row_number	rank	dense_rank
1	p01	100	*1	*1	*1
2	p01	90	*2	*2	*2
3	p01	90	*3	*2	*2
4	p01	80	*4	*4	*3
5	p01	80	*5	*4	*3
6	p01	80	6	*4	*3
7	p01	70	7	7	*4
8	p01	60	8	8	*5
9	p01	50	9	9	6
10	p01	40	10	10	7

这是一个包含编程相关内容的表格，其中有pkid、catid、value、row_number、rank和dense_rank六列。每一行代表了不同的数据，具体数值在表格中可以看到。

一旦您选择了函数，就可以像这样使用它：

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5

DB<>Fiddle

---

在MySQL 5.x中，您可以使用贫穷人的分区排名来实现所需的结果：将表与自身外连接，并为每个行计算其之前的行数（例如，之前的行可能是具有更高值的行）。以下内容将产生类似于“RANK”函数的结果：

SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid

进行以下更改以产生类似于DENSE_RANK函数的结果：

COUNT(DISTINCT b.value)

或者进行以下更改以产生类似于ROW_NUMBER函数的结果：

ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)

DB<>Fiddle

- Salman A

---

+1 你的答案改写非常有价值，因为现代的MySQL/MariaDB版本更加遵循ANSI/ISO SQL 1992/1999/2003标准，在派生/子查询中使用ORDER BY从来就不被允许。这就是为什么现代的MySQL/MariaDB版本在没有使用LIMIT的子查询中忽略ORDER BY的原因，我相信ANSI/ISO SQL标准2008/2011/2016在与FETCH FIRST n ROWS ONLY结合使用时，使得在派生/子查询中使用ORDER BY是合法的。 - Raymond Nijland

太好了，这个完美地运行了...我发现另一个解决方案（https://stackoverflow.com/a/48593547）使用相关子查询，那个也可以工作并产生相同的结果，但是我认为你的解决方案（使用连接）运行得更快。 - leo

Answer 2

您可以使用 GROUP_CONCAT 聚合函数将所有年份放入一个单独的列中，按 id 进行分组并按 rate 排序：

SELECT   id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
FROM     yourtable
GROUP BY id

结果：

-----------------------------------------------------------
|  ID | GROUPED_YEAR                                      |
-----------------------------------------------------------
| p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 |
| p02 | 2001,2004,2002,2003,2000,2006,2007                |
-----------------------------------------------------------

您可以使用FIND_IN_SET函数，它返回第一个参数在第二个参数中的位置，例如：

SELECT FIND_IN_SET('2006', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
1

SELECT FIND_IN_SET('2009', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
6

通过GROUP_CONCAT和FIND_IN_SET的组合，通过过滤find_in_set返回的位置，您可以使用此查询仅返回每个id的前5年：

SELECT
  yourtable.*
FROM
  yourtable INNER JOIN (
    SELECT
      id,
      GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
    FROM
      yourtable
    GROUP BY id) group_max
  ON yourtable.id = group_max.id
     AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5
ORDER BY
  yourtable.id, yourtable.year DESC;

请参见此处的示例：http://sqlfiddle.com/#!2/1c220/1。

请注意，如果多行可以具有相同的利率，则应考虑在“rate”列上使用GROUP_CONCAT(DISTINCT rate ORDER BY rate)而不是“year”列。 GROUP_CONCAT返回的字符串长度是有限制的，因此，如果您需要为每个组选择一些记录，则可以使用该方法。

- fthiella

---

非常感谢你，这真是“漂亮地”高效、相对简单并且解释得很好。关于你最后提到的问题，在能够计算出一个合理的最大长度时，可以使用 SET SESSION group_concat_max_len = <maximum length>;。在 OP 的情况下，这不是问题（因为默认值是 1024），但作为示例，group_concat_max_len 应至少为 25：4（一年字符串的最大长度）+1（分隔符字符）乘以 5（前五年）。这些字符串会被截断而不是抛出错误，所以要注意警告，例如 1054 rows in set, 789 warnings (0.31 sec)。 - Timothy Johns

如果我想获取确切的两行而不是1到5行，那么我应该在FIND_IN_SET()中使用什么？我尝试了FIND_IN_SET() =2，但结果并不如预期。 - Amogh

如果GROUP_CONCAT集合的大小等于或大于5，则FIND_IN_SET BETWEEN 1和5将获取前5个位置。因此，FIND_IN_SET = 2仅获取在GROUP_CONCAT中处于第二个位置的数据。如果有2行数据，并且要获取第1个和第2个位置，可以尝试使用BETWEEN 1和2。 - wheeleruniverse

这个解决方案在处理大型数据集时比Salman的表现要好得多。无论如何，我都为这些聪明的解决方案点了赞。谢谢！ - tiomno

关于“如果您需要为每个组选择几条记录，则此方法非常有效”：MySQL实际上是否会在字符串填满后避免读取更多数据？我怀疑它会首先将所有行加载到内存中，从而无论最大字符串长度如何都会冒着完全索引/表扫描的风险。如果我错了，我会很高兴的。 - Timo

哇！谈到MySql的黑魔法！这个解决方案真是太棒了，速度快得让人惊叹。谢谢你！ - undefined

Answer 3

对于我来说，像这样的东西

SUBSTRING_INDEX(group_concat(col_name order by desired_col_order_name), ',', N) 

运行完美。没有复杂的查询。

---

例如：获取每个组的前1个

SELECT 
    *
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY rate DESC),
                        ',',
                        1) id
        FROM
            yourtable
        GROUP BY year)
ORDER BY rate DESC;

- Vishal Kumar

---

你的解决方案完美地运行了，但我还想从子查询中检索年份和其他列，我们该怎么做？ - MaNn

Answer 4

SELECT year, id, rate
FROM (SELECT
  year, id, rate, row_number() over (partition by id order by rate DESC)
  FROM h
  WHERE year BETWEEN 2000 AND 2009
  AND id IN (SELECT rid FROM table2)
  GROUP BY id, year
  ORDER BY id, rate DESC) as subquery
WHERE row_number <= 5

子查询与您的查询几乎相同，唯一的区别是添加了

row_number() over (partition by id order by rate DESC)

- Ricky Moreno

---

这很好，但是MySQL没有窗口函数（如ROW_NUMBER()）。 - ypercubeᵀᴹ

截至 MySQL 8.0 版本，row_number() 可用。 - erickg

为了使示例按原样工作，只需将别名添加到行号：(row_number() over (partition by user_id order by created_at DESC)) as row_number - soniaseguz

Answer 5

不可以任意地限制子查询（在新的MySQL中有一定的限制，但不支持每个分组返回5个结果）。

这是一种分组最大值类型的查询，在SQL中不容易实现。有各种方法来解决这个问题，对于某些情况可能更有效率，但对于一般情况下的前n个结果，您需要查看Bill's answer以获取类似之前问题的答案。

与解决此问题的大多数解决方案一样，如果有多行具有相同的rate值，则可能会返回多于五行，因此您仍然需要进行后处理以进行检查。

- bobince

Answer 6

这需要一系列子查询来对值进行排名、限制并在分组时执行求和。

@Rnk:=0;
@N:=2;
select
  c.id,
  sum(c.val)
from (
select
  b.id,
  b.bal
from (
select   
  if(@last_id=id,@Rnk+1,1) as Rnk,
  a.id,
  a.val,
  @last_id=id,
from (   
select 
  id,
  val 
from list
order by id,val desc) as a) as b
where b.rnk < @N) as c
group by c.id;

- Brian L Cartwright

Answer 7

试试这个：

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;

- Saharsh Shah

---

字段列表中不存在 a.type 列。 - anu

Answer 8

Build the virtual columns（类似于Oracle中的RowID）

表：

CREATE TABLE `stack` 
(`year` int(11) DEFAULT NULL,
`id` varchar(10) DEFAULT NULL,
`rate` float DEFAULT NULL) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

数据：

insert into stack values(2006,'p01',8);
insert into stack values(2001,'p01',5.9);
insert into stack values(2007,'p01',5.3);
insert into stack values(2009,'p01',4.4);
insert into stack values(2001,'p02',12.5);
insert into stack values(2004,'p02',12.4);
insert into stack values(2005,'p01',2.1);
insert into stack values(2000,'p01',0.8);
insert into stack values(2002,'p02',12.2);
insert into stack values(2002,'p01',3.9);
insert into stack values(2004,'p01',3.5);
insert into stack values(2003,'p02',10.3);
insert into stack values(2000,'p02',8.7);
insert into stack values(2006,'p02',4.6);
insert into stack values(2007,'p02',3.3);
insert into stack values(2003,'p01',7.4);
insert into stack values(2008,'p01',6.8);

像这样的SQL：

select t3.year,t3.id,t3.rate 
from (select t1.*, (select count(*) from stack t2 where t1.rate<=t2.rate and t1.id=t2.id) as rownum from stack t1) t3 
where rownum <=3 order by id,rate DESC;

如果在t3中删除where子句，它会显示如下：

获取“前N个记录” --> 在 t3 的 where 子句中添加 rownum <= 3；

选择“年份” --> 在 t3 的 where 子句中添加 BETWEEN 2000 AND 2009；

- Wang Wen'an

---

如果您有相同ID重复的费率，则此方法将无法正常工作，因为行号计数会增加得更高；您将无法每行获得3个，而只能获得0、1或2个。您能想到任何解决方案吗？ - starvator

@starvator 将 "t1.rate<=t2.rate" 更改为 "t1.rate<t2.rate"，如果最佳利率在相同的id中具有相同的值，则所有这些值都具有相同的行号，但不会增加更高；例如 "id p01中的利率8"，如果重复使用 "t1.rate<t2.rate"，则两个 "id p01中的利率8" 都具有相同的行号0；如果使用 "t1.rate<=t2.rate"，则行号为2； - Wang Wen'an

Answer 9

花了一些时间，但我认为我的解决方案很优雅且非常快速。

SELECT h.year, h.id, h.rate 
  FROM (
    SELECT id, 
      SUBSTRING_INDEX(GROUP_CONCAT(CONCAT(id, '-', year) ORDER BY rate DESC), ',' , 5) AS l
      FROM h
      WHERE year BETWEEN 2000 AND 2009
      GROUP BY id
      ORDER BY id
  ) AS h_temp
    LEFT JOIN h ON h.id = h_temp.id 
      AND SUBSTRING_INDEX(h_temp.l, CONCAT(h.id, '-', h.year), 1) != h_temp.l

请注意，此示例是为了解答而指定的，并且可以轻松修改以满足其他类似目的。

- John

Answer 10

以下帖子：sql: 每组选择前N条记录描述了在没有子查询的情况下实现此操作的复杂方法。

它通过以下方式提高了其他解决方案的效果：

• 在单个查询中完成所有操作
• 能够正确地利用索引
• 避免使用子查询，因为众所周知，在MySQL中会产生糟糕的执行计划

然而，这种方法并不美观。如果MySQL启用了窗口函数（也称为分析函数），则可以实现更好的解决方案--但它们没有被启用。 该帖子中使用的技巧利用了GROUP_CONCAT，有时被描述为“MySQL的穷人版窗口函数”。

- Shlomi Noach