group by分组后,取每组的前几行

锋利的SQL：从分组中取前几行数据

sql 用Group by分组后,取每组的前几条记录

本文由以上两篇原文整理得出，请点击以上链接去到原文

原理是表的自身关联

下面的语句用于创建示例表：

CREATE TABLE Students

(ClassID int,

 StuNamechar(10),

 Achi int);

INSERT INTO Students

VALUES(1, '张山', 100),

      (1, '李明', 90),

      (1, '王磊', 95),

      (2, '孙科', 100),

      (2, '赵强', 80),

      (2, '王智', 90),

      (3, '李海', 95);

1.使用联接获取前几行

如果将Students表打开两次，将一个考生与其大于或等于自己成绩的考生联接，我们看看会得到什么样的结果。参考下面的语句：

SELECT S1.*, S2.*

FROM Students AS S1

  INNER JOINStudents AS S2

    ONS1.ClassID = S2.ClassID

      ANDS2.Achi >= S1.Achi

ORDER BY S1.ClassID, S1.Achi DESC;

结果如表2所示。

表2                                                                       联接结果

S1.ClassID	S1.StuName	S1.Achi	S2.ClassID	S2.StuName	S2.Achi
1	张山	100	1	张山	100
1	王磊	95	1	张山	100
1	王磊	95	1	王磊	95
1	李明	90	1	张山	100
1	李明	90	1	李明	90
1	李明	90	1	王磊	95
2	孙科	100	2	孙科	100
2	王智	90	2	孙科	100
2	王智	90	2	王智	90
2	赵强	80	2	孙科	100
2	赵强	80	2	赵强	80
2	赵强	80	2	王智	90
3	李海	95	3	李海	95

从上表中可以看出，1班中的第1名张山有1条记录，第2名王磊有2条记录，第3名有3条记录。因此，我们可以使用下面的语句来获取每班中前2名的考生。查询结果如表3所示。

SELECT S1.ClassID, S1.Achi, MAX(S1.StuName) ASStuName

FROM Students AS S1

  INNER JOINStudents AS S2

    ONS1.ClassID = S2.ClassID

      ANDS2.Achi >= S1.Achi

GROUP BY S1.ClassID, S1.Achi  

HAVING COUNT(*) <=2

ORDER BY S1.ClassID, S1.Achi DESC;

表3                                                             每班中前2名的考生

ClassID	Achi	StuName
1	100	张山
1	95	王磊
2	100	孙科
2	90	王智
3	95	李海

2. 使用子查询实现

select * from student s1
where (select COUNT(*) from student where ClassID=s1.ClassID and Achi>=s1.Achi) <=2

这一个的本质其实也是表的自身内关联，子查询里的student相当于"使用联接获取前几行"中例子的s1,"select * from student s1"中的student相当于"使用联接获取前几行"中例子的s2.