一道淘宝的考察sql语句的面试题

MySQL

SELECT a.* FROM `t_stu` a INNER JOIN ((SELECT gender,grade FROM `t_stu` WHERE gender=0 group BY grade DESC LIMIT 4,1) UNION ALL (SELECT gender,grade FROM `t_stu` WHERE gender=1 group BY grade DESC LIMIT 4,1)) b ON a.grade>=b.grade AND a.gender=b.gender ORDER BY gender,grade DESC

这样不行，当分数出现少于五个的情况

SELECT ROWNUM, V.*
   FROM (select * from t_stu t1 where t1.gender = 'M' order by t1.grade asc) V
  WHERE ROWNUM < 6
UNION ALL
SELECT ROWNUM, V.*
   FROM (select * from t_stu t1 where t1.gender = 'F' order by t1.grade asc) V
  WHERE ROWNUM < 6

-- Create table
create table T_STU
(
  ID     NUMBER,
  NAME   VARCHAR2(50),
  GENDER VARCHAR2(1),
  GRADE  NUMBER
)
tablespace PIP_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

这个不就是很简单的分析函数么？
select * from (
select name,grade,gender ,rank() over (partition by gender order by grade desc nulls last) rnk from t_stu
) where rnk<=5

我也发现了...只要加上括号就可以了

没必要分性别啊，除了男不就女么？难道还有人要？所以我感觉性别是误导你的。

这个题的本意应该是要用UNION合并两个查询，得到的效率也是最高的。如果不用UNION，我给了一个例子，在SQL SERVER中测试成功过的。

declare @t_stu table(
	id int primary key identity(1,1),
	name varchar(100),
	sex int,
	grade int
)

insert into @t_stu(name, sex, grade) values('xiaoming01', 0, 100)
insert into @t_stu(name, sex, grade) values('xiaoming2', 1, 99)
insert into @t_stu(name, sex, grade) values('xiaoming03', 0, 90)
insert into @t_stu(name, sex, grade) values('xiaoming04', 0, 80)
insert into @t_stu(name, sex, grade) values('xiaoming5', 1, 10)
insert into @t_stu(name, sex, grade) values('xiaoming6', 1, 100)
insert into @t_stu(name, sex, grade) values('xiaoming07', 0, 70)
insert into @t_stu(name, sex, grade) values('xiaoming8', 1, 50)
insert into @t_stu(name, sex, grade) values('xiaoming011', 0, 100)
insert into @t_stu(name, sex, grade) values('xiaoming12', 1, 99)
insert into @t_stu(name, sex, grade) values('xiaoming013', 0, 90)
insert into @t_stu(name, sex, grade) values('xiaoming014', 0, 80)
insert into @t_stu(name, sex, grade) values('xiaoming15', 1, 10)
insert into @t_stu(name, sex, grade) values('xiaoming16', 1, 100)
insert into @t_stu(name, sex, grade) values('xiaoming017', 0, 70)
insert into @t_stu(name, sex, grade) values('xiaoming18', 1, 50)

select * from @t_stu a where a.id in (select top 5 id from @t_stu b where a.sex = b.sex order by grade DESC)
	order by a.sex ASC, a.grade DESC

得到的结果如下：
id   name    sex         grade
------------------------------------
1    xiaoming01     0    100
9    xiaoming011    0    100
3    xiaoming03     0    90
11   xiaoming013    0    90
4    xiaoming04     0    80
6    xiaoming6      1    100
14   xiaoming16     1    100
10   xiaoming12     1    99
2    xiaoming2      1    99
8    xiaoming8      1    50

人才！

人才啊，运动会也别分男女了，大家一起比吧。

db2数据库：t_stu；其中三个字段:name,gender,grade
select * from  ( select info2.*  from  t_stu info2 where gender='1' order by grade asc fetch first 5 rows only ) as tb
union 
select  * from (select info1.* from  t_stu  info1 where gender='2' order by grade asc fetch first 5 rows only ) as tb1

select t3.* from (  
                    select t.* from (select name,grade,gender from t_stu t where gender = 0 order by grade desc limit 0, 5) t
                    union all
                    select t2.* from (select name,grade,gender from t_stu t where gender = 1 order by grade desc limit 0, 5) t2
                    ) t3 order by t3.gender,t3.grade desc
试试这个。