分组取前N记录 一道淘宝的考察sql语句的面试题

因为一道淘宝面试的sql语句题目开始思考如何取出每组的前N条记录，在网上找了2篇比较好的博文转载下！

 

经常看到问题，如何取出每组的前N条记录。方便大家参考于是便把常见的几种解法列出于下。

问题：有表 如下，要求取出各班前两名（允许并列第二）
Table1
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  1 |AAAA  |  C1  | 67  |
|  2 |BBBB  |  C1  | 55  |
|  3 |CCCC  |  C1  | 67  |
|  4 |DDDD  |  C1  | 65  |
|  5 |EEEE  |  C1  | 95  |
|  6 |FFFF  |  C2  | 57  |
|  7 |GGGG  |  C2  | 87  |
|  8 |HHHH  |  C2  | 74  |
|  9 |IIII  |  C2  | 52  |
| 10 |JJJJ  |  C2  | 81  |
| 11 |KKKK  |  C2  | 67  |
| 12 |LLLL  |  C2  | 66  |
| 13 |MMMM  |  C2  | 63  |
| 14 |NNNN  |  C3  | 99  |
| 15 |OOOO  |  C3  | 50  |
| 16 |PPPP  |  C3  | 59  |
| 17 |QQQQ  |  C3  | 66  |
| 18 |RRRR  |  C3  | 76  |
| 19 |SSSS  |  C3  | 50  |
| 20 |TTTT  |  C3  | 50  |
| 21 |UUUU  |  C3  | 64  |
| 22 |VVVV  |  C3  | 74  |
+----+------+------+-----+
结果如下
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  5 |EEEE  |  C1  | 95  |
|  1 |AAAA  |  C1  | 67  |
|  3 |CCCC  |  C1  | 67  |
|  7 |GGGG  |  C2  | 87  |
| 10 |JJJJ  |  C2  | 81  |
| 14 |NNNN  |  C3  | 99  |
| 18 |RRRR  |  C3  | 76  |
+----+------+------+-----+

方法一：

select a.id,a.SName,a.ClsNo,a.Score
from Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Score
group by a.id,a.SName,a.ClsNo,a.Score
having count(b.id)<2
order by a.ClsNo,a.Score desc

方法二：

select *
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc

方法三：

select *
from Table1 a
where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)
order by a.ClsNo,a.Score desc

方法....

这里列出了多种SQL语句的实现方法，有些是MySQL特有的(Limit, 其它数据库可根据实际更改，比如oracle的rownum,MS SQL SERVER 的 top,..)，有时是SQL标准支持的。但效率上和应用的场合或许不同。具体应用时可根据实际表中的记录情况，索引情况进行选择。

特例 N=1 ,即取最大的/最小的一条记录。
+----+------+------+-----+
| id |SName |ClsNo |Score|
+----+------+------+-----+
|  5 |EEEE  |  C1  | 95  |
|  7 |GGGG  |  C2  | 87  |
| 14 |NNNN  |  C3  | 99  |
+----+------+------+-----+

select * 
from Table1 a
where not exists (select 1 from Table1 where ClsNo=a.ClsNo and Score>a.Score);

 


select a.* 
from Table1 a inner join (select ClsNo, max(Score) as mScore from Table1 group by ClsNo) b
 on a.ClsNo=b.ClsNo and a.Score=b.Score

 

select *
from (select * from Table1 order by Score desc) t
group by ClsNo

转自：http://blog.csdn.net/acmain_chm/article/details/4126306

 

现引入一道淘宝的sql语句面试题：要求用一条sql语句查出按grade排名男生前5名和女生前5名

CREATE TABLE `t_stu` (  
`id` int(4) NOT NULL DEFAULT '0',  
`name` varchar(16) DEFAULT NULL,  
`gender` int(2) DEFAULT NULL,  
`grade` int(4) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
)DEFAULT CHARSET=utf8;


insert into t_stu values(1,"ElenaA",0,90);  
insert into t_stu values(2,"ElenaB",1,92);
insert into t_stu values(3,"ElenaC",1,20);
insert into t_stu values(4,"ElenaD",0,80);
insert into t_stu values(5,"ElenaE",1,20);
insert into t_stu values(6,"ElenaF",0,40);
insert into t_stu values(7,"ElenaG",0,50);
insert into t_stu values(8,"ElenaH",1,20);
insert into t_stu values(9,"ElenaI",0,30);
insert into t_stu values(10,"ElenaG",1,12);
insert into t_stu values(11,"ElenaK",0,42);
insert into t_stu values(12,"ElenaM",1,52);
insert into t_stu values(13,"ElenaN",0,62);
insert into t_stu values(14,"ElenaO",1,72);
insert into t_stu values(15,"ElenaP",1,22);
insert into t_stu values(16,"ElenaQ",1,12);
insert into t_stu values(17,"ElenaR",0,82);
insert into t_stu values(18,"ElenaS",0,99);

#抽取出来的4种解决办法
#1
select * from t_stu a where 5>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc  

#2
select * from t_stu where grade in (select * from ( (select distinct(grade) from t_stu where gender=1 order by grade desc limit 5) a)) and gender=1;

#3
SELECT a.* FROM t_stu a INNER JOIN ((SELECT GROUP_CONCAT(Id) AS Id FROM t_stu where gender=1 GROUP BY grade DESC LIMIT 5) union all (SELECT GROUP_CONCAT(Id) AS Id FROM t_stu where gender=0 GROUP BY grade DESC LIMIT 5)) b ON FIND_IN_SET(a.id,b.Id)>0 ORDER BY grade DESC ;

#4
SELECT * FROM `t_stu` a WHERE grade>=IFNULL((SELECT grade FROM `t_stu` WHERE gender=a.gender order BY grade DESC LIMIT 4,1),0);

转自：http://topic.csdn.net/u/20120515/14/a84130bd-3a2e-4810-a229-0e5394c43387.html