论坛首页 → 综合技术论坛 →

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

浏览 55199 次
锁定老帖子 主题：一道淘宝的考察sql语句的面试题
精华帖 (0) :: 良好帖 (3) :: 新手帖 (0) :: 隐藏帖 (0)
作者 正文
  • tfwin2
  • 等级: 初级会员
  • 性别:
  • 文章: 72
  • 积分: 50
  • 来自: 南京
   发表时间：2011-05-07  
lianglaiyang 写道
设计了一组数据，上面的答案都不正确，最大的问题是，分数前五名的同学，人数不止5个人。
INSERT INTO `t_stu` VALUES ('1', 'Alex', '1', '91');
INSERT INTO `t_stu` VALUES ('2', 'Elena', '0', '92');
INSERT INTO `t_stu` VALUES ('3', '33', '1', '92');
INSERT INTO `t_stu` VALUES ('4', '44', '1', '92');
INSERT INTO `t_stu` VALUES ('5', '55', '1', '98');
INSERT INTO `t_stu` VALUES ('6', '66', '1', '98');
INSERT INTO `t_stu` VALUES ('7', '77', '1', '98');
INSERT INTO `t_stu` VALUES ('8', '88', '1', '92');


哦，呵呵，还是题目没理解好，这样的话用一条SQL应该就复杂多了，我再想想！
返回顶楼
          回帖地址
0 请登录后投票
  • tfwin2
  • 等级: 初级会员
  • 性别:
  • 文章: 72
  • 积分: 50
  • 来自: 南京
   发表时间：2011-05-07   最后修改：2011-05-07
想不出来，放弃了，关键是第四，第五，第六的成绩一样的话，取第5名的成绩做为判断条件没有意义，
如果非要一条SQL的话，我只想到用分析函数RANK应该可以解决，坐等答案了。 

--rank()
SELECT * FROM(SELECT RANK() OVER(PARTITION BY GENDER ORDER BY GRADE DESC) RANK,A.* FROM  T_STU A )WHERE RANK<=5;
返回顶楼
          回帖地址
0 请登录后投票
  • 小小风信子
  • 等级: 初级会员
  • 性别:
  • 文章: 12
  • 积分: 30
  • 来自: 杭州
   发表时间：2011-05-07   最后修改：2011-05-07
如果是使用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]
  • 大小: 38.3 KB
返回顶楼
          回帖地址
0 请登录后投票
  • yhz61010
  • 等级: 初级会员
  • 文章: 2
  • 积分: 60
  • 来自: -
   发表时间：2011-05-07  
select t_stu.name, t_stu.grade, t_stu.gender
from t_stu
where gender = 1 and grade in
(select distinct(grade) as mark from t_stu where gender = 1 order by grade desc limit 5)
or gender = 0 and grade in
(select distinct(grade) as mark from t_stu where gender = 0 order by grade desc limit 5)
order by gender, grade desc

楼主可以验证下，看看上面这条SQL写的对不对。
返回顶楼
          回帖地址
0 请登录后投票
  • sniffer123
  • 等级: 初级会员
  • 性别:
  • 文章: 145
  • 积分: 0
  • 来自: 上海
   发表时间：2011-05-07  
小小风信子 写道
如果是使用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]

太久没用oracle 都忘了有窗口查询了
返回顶楼
          回帖地址
0 请登录后投票
  • tfwin2
  • 等级: 初级会员
  • 性别:
  • 文章: 72
  • 积分: 50
  • 来自: 南京
   发表时间：2011-05-07  
DENSE_RANK() 跟RANK还是有不同的，不知道楼主的题目是指哪一种了，明显DENSE_RANK() 要多很多人，呵呵，不过他的名次是不跳级的，RANK()会跳过相同排名的次数。
返回顶楼
          回帖地址
0 请登录后投票
  • czwlucky
  • 等级: 初级会员
  • 性别:
  • 文章: 128
  • 积分: 50
  • 来自: 河南郑州
   发表时间：2011-05-07  
小小风信子 写道
如果是使用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
返回顶楼
          回帖地址
0 请登录后投票
  • yuehan
  • 等级: 初级会员
  • 性别:
  • 文章: 10
  • 积分: 30
  • 来自: 成都
   发表时间：2011-05-07  
SilenceGG 写道
isaacfu_454 写道

 

 select * from t_stu a where 5>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc
 

+1


此写法性能极差！

首先全表扫描，每次扫描都会嵌套count统计.
返回顶楼
          回帖地址
0 请登录后投票
  • yuanyu5237
  • 等级: 初级会员
  • 性别:
  • 文章: 8
  • 积分: 40
  • 来自: 北京
   发表时间：2011-05-07  
谢谢各位老大的回复，我只学过一学期oracle，后来就没怎么用过，知道rownow可以查前几名，但是开窗函数不知道，我好好学一下，sqlserver里有top，mysql里有limit都可求前几名，题目是要求把男生中的分数前五和女生中的分数前五查出来，如果用两条语句就很简单，我想难的是如何只用一条语句，再次感谢各位前辈的回复，我先试一下mysql和sqlserver的答案，目前oracle刚刚装好，要先学一下开窗函数。还有不知道为什么我答案里那个union的关键字报错。
返回顶楼
          回帖地址
0 请登录后投票
  • jiangkefei
  • 等级: 初级会员
  • 性别:
  • 文章: 4
  • 积分: 70
  • 来自: 成都
   发表时间：2011-05-07  
SELECT TOP 5 * FROM T_STU T1,T_STU T2 WHERE T1.GENDER=1 AND T2.GENDER=0 ORDER BY T1.GRADE DESC,T2.GRADE DESC;
mssql的语法，
查出的结果里，T1为男生前五名的，T2为女生前五名的。
返回顶楼
          回帖地址
0 请登录后投票
论坛首页 → 综合技术版

跳转论坛:
Global site tag (gtag.js) - Google Analytics