最多选择每个组的x行 [英] Select up to x rows of each group

问题描述

使用以下表格：

  id |名称|工作|等级
 01约翰老师4 
 02老师2 
 03 phil plummer 1 
 04 dave老师7 
 05 jim plummer 9 
 06 bill bill plummer 2 
  

如何在每个作业中选择最多2行（如果可能，这样每个组的最低两位排名被选中）。我要找的结果是：

pre $ 02老师2
01老师4
03 phil plummer 1
06 bill plummer 2


基本上按工作分组，到2并按排名排序。我一直在尝试与GROUP BY以及LEFT JOIN，但我无法弄清楚如何做到这一点。使用GROUPING BY作业创建作业的临时列表时，我如何加入多次作业？

解决方案

 SELECT id，name，job，rank 
 FROM TableName a 
 WHERE 
（
 SELECT COUNT（*）
 FROM TableName as f 
其中f.job = a.job和
 f.rank <= a.rank 
）<= 2; 
  

• SQLFiddle演示

With a table of:

id | name       | job      | rank
01   john         teacher    4
02   mark         teacher    2
03   phil         plummer    1
04   dave         teacher    7
05   jim          plummer    9
06   bill         plummer    2

How can I select up to 2 rows of each job (if possible sorted by rank ASC in each group, so that the lowest two ranking of each group get picked). The result I'd be looking for is:

02 mark teacher 2
01 john teacher 4
03 phil plummer 1
06 bill plummer 2

This basically groups by job, with a limit to 2 and sorted by rank. I've been trying with GROUP BY as well as LEFT JOIN, but I just can't figure out how to do this. When creating a "temporary list" of jobs with GROUPING BY job, how do I join more than once onto that job?

解决方案

SELECT  id, name, job, rank
FROM    TableName a
WHERE 
        (
           SELECT   COUNT(*) 
           FROM     TableName as f
           WHERE    f.job = a.job AND 
                    f.rank <= a.rank
        ) <= 2;

• SQLFiddle Demo

这篇关于最多选择每个组的x行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！