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

接触的业务多了，写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);

结果：

1	111	男	99
13	888	男	99
12	777	男	99
6	011	女	99
16	088	女	99
15	077	女	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
) ;

结果：

1	111	男	99
2	222	男	98
3	333	男	97
11	666	男	99
12	777	男	99
13	888	男	99
6	011	女	99
7	022	女	98
8	033	女	97
14	066	女	99
15	077	女	99
16	088	女	99