答复: 一道淘宝的考察sql语句的面试题-CSDN博客

本文链接：https://blog.csdn.net/xiaoxiaofengxinzi2/article/details/83916800

如果是使用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]http://dl.iteye.com/upload/attachment/477276/8d8c484a-8273-366f-9173-9ffc5cb66c3f.jpg[/img]
[/img]