在GROUP BY中使用LIMIT获得每个组N个结果? [英] Using LIMIT within GROUP BY to get N results per group?
问题描述
以下查询:
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?
推荐答案
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;
请在此处看到小提琴.
请注意,如果可以有多个行具有相同的费率,则应考虑在费率列而不是年份列上使用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个结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!