問題描述

以下查詢:

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 個結果:

What I'd like is only the top 5 results for each id:

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 的修飾符來做到這一點?

Is there a way to do this using some kind of LIMIT like modifier that works within the GROUP BY?

推薦答案

你可以使用 GROUP_CONCAT 聚合函數將所有年份放入一個列中，按 id 分組并按 rate 排序:

You could use GROUP_CONCAT aggregated function to get all years into a single column, grouped by id and ordered by 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，返回第一個參數在第二個參數中的位置，例如.

And then you could use FIND_IN_SET, that returns the position of the first argument inside the second one, eg.

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 年:

Using a combination of GROUP_CONCAT and FIND_IN_SET, and filtering by the position returned by find_in_set, you could then use this query that returns only the first 5 years for every id:

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;

請參閱 fiddle 此處.

Please see fiddle here.

請注意，如果多于一行可以具有相同的費率，您應該考慮在費率列而不是年份列上使用 GROUP_CONCAT(DISTINCT rate ORDER BY rate).

Please note that if more than one row can have the same rate, you should consider using GROUP_CONCAT(DISTINCT rate ORDER BY rate) on the rate column instead of the year column.

GROUP_CONCAT 返回的字符串的最大長度是有限的，因此如果您需要為每個組選擇幾條記錄，這很有效.

The maximum length of the string returned by GROUP_CONCAT is limited, so this works well if you need to select a few records for every group.

這篇關于在 GROUP BY 中使用 LIMIT 以獲得每組 N 個結果?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！