http://www.iteye.com/topic/1029921?page=8
测试脚本如下：
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数据库
6 个解决方案
#1
要求的结果是什么
#2
一般都用第一种吧
#3
分组取前N条，论坛有帖子
#4
要求是啥哦
#5
有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。
#1
要求的结果是什么
#2
一般都用第一种吧
#3
分组取前N条，论坛有帖子
#4
要求是啥哦
#5
有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。