sql - 在GROUP BY中使用LIMIT获得每个组N个结果？

以下查询：

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

有没有一种方法可以使用在GROUP BY中起作用的某种LIMIT之类的修饰符？

最佳答案

您可以使用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;

请参阅小提琴here。

请注意，如果可以有多个行具有相同的费率，则应考虑在费率列而非年份列中使用GROUP_CONCAT（DISTINCT rate ORDER BY rate）。

GROUP_CONCAT返回的字符串的最大长度是有限的，因此，如果您需要为每个组选择一些记录，则此方法很好用。

关于sql - 在GROUP BY中使用LIMIT获得每个组N个结果？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35096933/