|
锁定老帖子 主题：一道淘宝的考察sql语句的面试题
精华帖 (0) :: 良好帖 (3) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2011-05-07
yuehan 写道
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统计.
|
| 返回顶楼 | |
|
发表时间：2011-05-08
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) 这个不是想要的结果吗? 这个就可以吧？我也是这么想的。lz有什么意见么？这样写的话？ |
| 返回顶楼 | |
|
发表时间：2011-05-08
select name,gender,grade from (
select name , gender , grade ,row_number() over(partition by gender order by grade desc ) n from t_stu ) where n<=5 ; row_number() over(partition by gender order by grade desc )在oracle下可用表示按照 gender分组，然后没组里面按照grade排序 |
| 返回顶楼 | |
|
发表时间：2011-05-08
--mysql
select * from t_stu where ID in (select ID from t_stu where gender='0' an d order by grade desc limit 5) or ID in (select ID from t_stu where gender='1' a nd grade >90 order by grade desc limit 5) order by gender; 这样？？ |
| 返回顶楼 | |
|
发表时间：2011-05-09
最后修改：2011-05-09
select * from t_stu
where (gender='0' and grade >=select min(grade) from t_stu where gender='0' and rownum<=5 order by grade desc) or(gender='1' and grade >=select min(grade) from t_stu where gender='1' and rownum<=5 order by grade desc) order by gender,grade desc 时间久了不用数据库，有点手生。。。。。 用子查询查找前5的最低分数，然后把所有大于这个分数的数据列出来就行了。感觉应该还行吧。少于5人的情况也能处理。。。。。。 |
| 返回顶楼 | |
|
发表时间：2011-05-09
select * from s_stu
where name in ( select name from s_stu where gender='男' and rownum<6 order grade ) or name in ( select name from s_stu where gender='女' and rownum<6 order grade ) |
| 返回顶楼 | |
|
发表时间：2011-05-09
oracle下的结果，执行是成功了，但是很土：
select * from (select * from S_FIELD where table_name='ST_DLTB' order by LIST_ORDER desc) b where rownum<6 union select * from (select * from S_FIELD where table_name='ST_DLTB_BG' order by LIST_ORDER desc) c where rownum<6 |
| 返回顶楼 | |
|
发表时间：2011-05-09
小小风信子 写道 如果是使用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] 这个不错，看样子自己需要学习了。。 |
| 返回顶楼 | |
|
发表时间：2011-05-09
呵呵，都是人才啊
|
| 返回顶楼 | |
|
发表时间：2011-05-09
mysql
SELECT a.* FROM (SELECT NAME FROM t_stu WHERE gender = 1 ORDER BY grade LIMIT 2)a UNION ALL SELECT a.* FROM (SELECT NAME FROM t_stu WHERE gender = 0 ORDER BY grade LIMIT 2)a oracle
select a.* from ( select name,grade, row_number() over(partition by gender order by grade) as rn from t_stu ) a where a.rn < 6 |
| 返回顶楼 | |
