选出男生和女生前三名的信息

来源：互联网发布：日向日足知乎编辑：程序博客网时间：2024/04/30 12:01

接触的业务多了，写sql也慢慢熟悉起来。最近见到一个题：用一条sql语句选出一个班男生和女生前三名的信息。

sql如下：

</pre><pre name="code" class="html">mysql建表：create table students(                      id int(10) NOT NULL AUTO_INCREMENT,                      name varchar(20),              sex varchar(10),                      fenshu int(5),                      primary key (id)                     )engine=innodb default charset=utf8 auto_increment=1;insert into students(name,sex,fenshu)values('111','男',99);insert into students(name,sex,fenshu)values('222','男',98);insert into students(name,sex,fenshu)values('333','男',97);insert into students(name,sex,fenshu)values('444','男',96);insert into students(name,sex,fenshu)values('555','男',95);insert into students(name,sex,fenshu)values('666','男',99);insert into students(name,sex,fenshu)values('777','男',99);insert into students(name,sex,fenshu)values('888','男',99);insert into students(name,sex,fenshu)values('011','女',99);insert into students(name,sex,fenshu)values('022','女',98);insert into students(name,sex,fenshu)values('033','女',97);insert into students(name,sex,fenshu)values('044','女',96);insert into students(name,sex,fenshu)values('055','女',95);insert into students(name,sex,fenshu)values('066','女',99);insert into students(name,sex,fenshu)values('077','女',99);insert into students(name,sex,fenshu)values('088','女',99);

第一种写法，不管并列：

(select * from students where sex = '男' order by fenshu desc limit 3 ) union all(select * from students where sex = '女' order by fenshu desc limit 3);

结果：

1111男9913888男9912777男996011女9916088女9915077女99

第二种写法，算并列：

(select s.* from students s,  (select T1.fenshu from     (select count(*),fenshu from students where sex = '男' group by(fenshu) order by fenshu desc limit 3)T1   )T11 where s.fenshu in(T11.fenshu) and s.sex = '男' order by s.fenshu desc) union all(select s1.* from students s1,  (select T2.fenshu from       (select count(*),fenshu from students where sex = '女' group by(fenshu) order by fenshu desc limit 3)T2   )T22  where s1.fenshu in(T22.fenshu) and s1.sex = '女'  order by s1.fenshu desc) ;

结果：

1111男992222男983333男9711666男9912777男9913888男996011女997022女988033女9714066女9915077女9916088女99

0 0