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

有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。

小弟当时没做出来，只用最笨的办法解决：

select name,grade from t_stu m where gender = 1 order by grade limit 1,2 union select name,grade from t_stu f where gender = 0 order by grade limit 1,2;

但回来后，发现这个也是错的，说union和order by错误用法，想了很久，终于有一个答案：

select f.name,f.gender,f.grade,m.name,m.gender,m.grade,(f.grade+m.grade) gg from t_stu m, t_stu f where m.gender != f.gender m.name != f.name group by gg order by gg limit 1,3;

运行这个之后，感觉很不爽。希望看到这篇文章的朋友能给我找到一个好的答案。
还有，如果用oracle数据库怎么实现，还没试过，下面是我在mysql中的建表语句：

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,"Alex",1,91);
insert into t_stu values(2,"Elena",0,92);

附另外一道在别处碰到的sql题：
有一张表t_game，记录了游戏玩家的id，游戏玩家名name，和每个玩家玩的游戏game，请找出玩游戏最多的那个玩家，我用的子查询，先count每个游戏玩家玩的游戏数，然后max最大游戏数的玩家名。

评论

50 楼 miaofm 2011-05-13  

<div class="quote_title">tfwin2 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p><br>这位的效果更好！</p>
</div>
<p><br>就是换种思维方式，先算比某个成绩大的有几个，只要找出比他大的数小于5就可以了，只要知道这个思维，你不这样写也可以写出来，这样写看起来比较难懂点就是了</p>

49 楼 jetseter 2011-05-13  

select * from (
select t1.name as ‘姓名’,t1.gender as ‘性别’,t1.grade as ‘成绩’,decode('',null,rownum,'') as n1 from t_stu t1  where t1.gender=1 and rownum<=5  order by  t1.grade desc
) nn1 
left join 
(
select t2.name as ‘姓名’,t2.gender as ‘性别’,t2.grade as ‘成绩’,decode('',null,rownum,'') as n2   from t_stu t2 where t2.gender=0 and rownum<=5  order by  t2.grade desc
) nn2 on nn1.n1 = nn2.n2

48 楼 liweizhouzhang 2011-05-13  

还有 我看你们都在弄什么排名问题，题目又没说什么排名，你们走入误区了，就是各取5个人。就这个意思 别复杂化了

47 楼 liweizhouzhang 2011-05-13  

oracle 中 group by having for 应该就能实现要求呀

46 楼 lich0079 2011-05-13  

<div class="quote_title">287854442 写道</div>
<div class="quote_div">
<div class="quote_title">hongmin118 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>能解释下这段SQL么？</p>
<p>我看不懂。。。</p>
</div>
<p>小小解释一下：</p>
<p>首先sql语句可以变换为：    （order by a.grade desc是对最后的结果起排序作用的，可以不要。） </p>
<p> </p>
<pre name="code" class="sql">select * from t_stu a where (select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) &lt; 5 order by a.grade desc </pre>
<p> </p>
<p>对于t_stu的每一行 比如说 'xueshi',1,80：</p>
<p>得到t_stu表里性别是1，分数比80分高的记录数，如果得到的记录的数目小于5就保留，继续扫描下一行。否则跳过继续扫描下一行。</p>
<p> </p>
<p>这样看来确实存在性能问题。 不知道mysql会不会自动对这个进行优化。</p>
<p> </p>
</div>
<p> </p>
<p> </p>
<p>对这个sql的理解是 对于每一行t_stu的记录， 拿出该条记录与一个新的t_stu做join, 然后找出这里面性别和自己一样，但分数比自己高的记录，然后统计这些记录的条数， 假如比自己高的记录数少于5，自己当然就是前5名了</p>
<p>原文在  http://renxiangzyq.iteye.com/blog/1038841</p>

45 楼 sbkyv 2011-05-11  

MYSQL
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

44 楼 hoton 2011-05-11  

<div class="quote_title">hongmin118 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>能解释下这段SQL么？</p>
<p>我看不懂。。。</p>
</div>
<p><br>标准的sql写法</p>

43 楼 287854442 2011-05-11  

<div class="quote_title">hongmin118 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>能解释下这段SQL么？</p>
<p>我看不懂。。。</p>
</div>
<p>小小解释一下：</p>
<p>首先sql语句可以变换为：    （order by a.grade desc是对最后的结果起排序作用的，可以不要。） </p>
<p> </p>
<pre name="code" class="sql">select * from t_stu a where (select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) &lt; 5 order by a.grade desc </pre>
<p> </p>
<p>对于t_stu的每一行 比如说 'xueshi',1,80：</p>
<p>得到t_stu表里性别是1，分数比80分高的记录数，如果得到的记录的数目小于5就保留，继续扫描下一行。否则跳过继续扫描下一行。</p>
<p> </p>
<p>这样看来确实存在性能问题。 不知道mysql会不会自动对这个进行优化。</p>
<p> </p>

42 楼 287854442 2011-05-11  

<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>+1</p>
<p>+1</p>

41 楼 287854442 2011-05-11  

xiangzi21 写道

(select * from t_stu where gender = 0 order by grade desc limit 5)
union all
(select * from t_stu where gender = 1 order by grade desc limit 5)
这个不是想要的结果吗?

OK。

40 楼 lyw985 2011-05-11  

小小风信子 写道

如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法：

CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 

SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]

前三个是并列第1的话，第4个应该是第4名

39 楼 hongmin118 2011-05-11  

<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>能解释下这段SQL么？</p>
<p>我看不懂。。。</p>

38 楼 lumi 2011-05-11  

呵呵，大家的思路都很好，不过我觉得这个问题用union足够了，好理解，性能也过得去

37 楼 kingkan 2011-05-11  

<div class="quote_title">denny9527 写道</div>
<div class="quote_div">
<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>不错！！！</p>
</div>
<p><br>非oracle的话，这条SQL还可以，不过数据量大的话还是有性能问题，用存储过程拆开前五的grade的值也是一种方案。</p>

36 楼 kingkan 2011-05-11  

czwlucky 写道

小小风信子 写道

如果是使用oracle的话，应该是考查分析函数和开窗函数的使用，最近一直在写这个，现在给出oracle的写法：

CREATE TABLE T_STU(  
       ID NUMBER(38) PRIMARY KEY, 
       NAME VARCHAR2(20),  
       GENDER NUMBER(1),  
       GRADE NUMBER(4)  
); 

insert into t_stu values(1,'Alex',1,91);     
insert into t_stu values(2,'Elena',0,92);    
insert into t_stu values(3,'Alex2',1,92);     
insert into t_stu values(4,'Elena2',1,92);    
insert into t_stu values(5,'Alex3',1,98);     
insert into t_stu values(6,'Elena3',1,98);    
insert into t_stu values(7,'Alex4',1,98);     
insert into t_stu values(8,'Elena4',1,92);    
insert into t_stu values(9,'Alex5',1,51);     
insert into t_stu values(10,'Elena5',0,90);    
insert into t_stu values(11,'Alex6',1,90);     
insert into t_stu values(12,'Elena6',0,90);   
insert into t_stu values(13,'Elena7',0,89);  
insert into t_stu values(14,'Elena8',0,87); 
insert into t_stu values(15,'Alex5',1,60); 
insert into t_stu values(16,'Alex5',1,70); 

SELECT * 
  FROM (SELECT s.*,
               DENSE_RANK() OVER(PARTITION BY s.gender ORDER  BY s.grade DESC) dro 
	       FROM t_stu s ) t
 WHERE t.dro <= 5

下面的图片是执行sql后的效果，当然最后一列是不需要的，这里显示只是为了让你明白
[img]

[/img]

不知道前五名是如何定义的，可以参考三个分析函数进行查询，具体使用哪一个，就看是如何定义这个“前五名”的。
row_number() 按序分配序列,如: 1, 2 ,3 ,4 ,5
rank() 并列排名会占取下一个名次，如： 1, 2, 2, 4, 5
dense_rank() 并列名次不会占取下一名次,如：1, 2, 2, 3, 4, 5

+1，这个回答已经很全面了

35 楼 fflame 2011-05-10  

看了回贴，发现考虑的内容少了，哈哈。题干表述的确也是不大明确

34 楼 sf1816 2011-05-09  

不会数据库分析函数，oracle数据库，常规写法，仅实现功能，未考虑效率问题

查询出分数前5的男生和分数前5的女生，不论人数有多少，只要满足分数前5，都查出来

SELECT * FROM T_STU T WHERE
(T.GRADE IN (SELECT S1.GRADE FROM ( SELECT DISTINCT(T1.GRADE) FROM T_STU T1  WHERE T1.GENDER=1 ORDER BY T1.GRADE DESC) S1 WHERE ROWNUM<=5) AND T.GENDER=1)
OR
(T.GRADE IN (SELECT S2.GRADE FROM ( SELECT DISTINCT(T2.GRADE) FROM T_STU T2  WHERE T2.GENDER=0 ORDER BY T2.GRADE DESC) S2 WHERE ROWNUM<=5) AND T.GENDER=0)
ORDER BY T.GENDER,GRADE DESC

33 楼 seanla 2011-05-09  

xiangzi21 写道

(select * from t_stu where gender = 0 order by grade desc limit 5)
union all
(select * from t_stu where gender = 1 order by grade desc limit 5)
这个不是想要的结果吗?

大数据量的情况下执行效率太低

32 楼 勒布朗 2011-05-09  

select * from (select a.* ,row_number() over(partiton by a.gender order by a.grade) rn from t_stu)t where t.rn < 6;

31 楼 denny9527 2011-05-09  

<div class="quote_title">isaacfu_454 写道</div>
<div class="quote_div">
<p> </p>
<pre name="code" class="sql"> select * from t_stu a where 5&gt;(select count(*) FROM t_stu where gender=a.gender AND grade&gt;a.grade) order by a.grade desc</pre>
 </div>
<p>不错！！！</p>