分组取前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 |
+----+------+------+-----+
方法一：
1 select a.id,a.SName,a.ClsNo,a.Score 2 from Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Score 3 group by a.id,a.SName,a.ClsNo,a.Score 4 having count(b.id)<2 5 order by a.ClsNo,a.Score desc
方法二：
1 select * 2 from Table1 a 3 where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score) 4 order by a.ClsNo,a.Score desc
方法三：
1 select * 2 from Table1 a 3 where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2) 4 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 |
+----+------+------+-----+
1 select * 2 from Table1 a 3 where not exists (select 1 from Table1 where ClsNo=a.ClsNo and Score>a.Score); 4 5 6 8 9 select a.* 10 from Table1 a inner join (select ClsNo, max(Score) as mScore from Table1 group by ClsNo) b 11 on a.ClsNo=b.ClsNo and a.Score=b.Score 12 13 14 15 select * 16 from (select * from Table1 order by Score desc) t 17 group by ClsNo
转自：http://blog.csdn.net/acmain_chm/article/details/4126306
现引入一道淘宝的sql语句面试题：要求用一条sql语句查出按grade排名男生前5名和女生前5名
1 CREATE TABLE `t_stu` ( 2 `id` int(4) NOT NULL DEFAULT '0', 3 `name` varchar(16) DEFAULT NULL, 4 `gender` int(2) DEFAULT NULL, 5 `grade` int(4) DEFAULT NULL, 6 PRIMARY KEY (`id`) 7 )DEFAULT CHARSET=utf8; 8 9 10 insert into t_stu values(1,"ElenaA",0,90); 11 insert into t_stu values(2,"ElenaB",1,92); 12 insert into t_stu values(3,"ElenaC",1,20); 13 insert into t_stu values(4,"ElenaD",0,80); 14 insert into t_stu values(5,"ElenaE",1,20); 15 insert into t_stu values(6,"ElenaF",0,40); 16 insert into t_stu values(7,"ElenaG",0,50); 17 insert into t_stu values(8,"ElenaH",1,20); 18 insert into t_stu values(9,"ElenaI",0,30); 19 insert into t_stu values(10,"ElenaG",1,12); 20 insert into t_stu values(11,"ElenaK",0,42); 21 insert into t_stu values(12,"ElenaM",1,52); 22 insert into t_stu values(13,"ElenaN",0,62); 23 insert into t_stu values(14,"ElenaO",1,72); 24 insert into t_stu values(15,"ElenaP",1,22); 25 insert into t_stu values(16,"ElenaQ",1,12); 26 insert into t_stu values(17,"ElenaR",0,82); 27 insert into t_stu values(18,"ElenaS",0,99); 28 29 #抽取出来的4种解决办法 30 #1 31 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 32 33 #2 34 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; 35 36 #3 37 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 ; 38 39 #4 40 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
