主题:一道淘宝的考察sql语句的面试题
原文链接:
http://www.iteye.com/topic/1029921?page=8
测试脚本如下:
-
SQL code
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);
看看大家还能提出什么新见解。限MYSQL数据库
要求的结果是什么
一般都用第一种吧
分组取前N条,论坛有帖子
参考下贴中的多种方法
http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集,及散分….