来写个sql 玩玩。。

hh7yx 2013-04-27 05:29:07

看到的一个题目，看下CSDN有多少开发达人。

要求：
1.不用order by求出emp表中的工资最高的前五名。
2.用1条sql实现

...全文

361 点赞收藏 21

写回复

21 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

绝望依然 2013-06-05

这样的题目我们有很多，要不我随便给你看一个题目。老师说不做要求有点难。题目：求平均薪水的等级最低的部门名称。

回复赞

Iam太陽神 2013-05-17

引用 12 楼 u010412956 的回复:

[quote=引用 9 楼 buryMyLove 的回复:] 不用order by 用group by 行么，b字段是工资 select * from temp t1 where t1.b >= (select max(b) from (select t.b from temp t group by t.b) where rownum <= (select count(*) from temp) - 4);

嗯，答案是正确的，不过还可以简化[/quote] 我测试了一下。只能获取其中一条数据。并非前五名 Oracle11g

回复赞

ICE-word 2013-05-16

引用 12 楼 u010412956 的回复:

[quote=引用 9 楼 buryMyLove 的回复:] 不用order by 用group by 行么，b字段是工资 select * from temp t1 where t1.b >= (select max(b) from (select t.b from temp t group by t.b) where rownum <= (select count(*) from temp) - 4);

嗯，答案是正确的，不过还可以简化[/quote] 不行吧！如果max(b) 小于 (select count(*) from temp) 这个值就不行了！

回复赞

hh7yx 2013-05-02

引用 17 楼 restbely 的回复:

rank型 SELECT GZ FROM (SELECT ROW_NUMBER() OVER() AS RN, MAX(GZ) OVER(PARTITION BY GZ) AS GZ FROM EMP) WHERE RN BETWEEN (SELECT COUNT(1) - 4 FROM EMP) AND (SELECT COUNT(1) FROM EMP) dense_rank型 SELECT GZ FROM EMP WHERE GZ IN (SELECT GZ FROM (SELECT ROW_NUMBER() OVER() AS RN, MAX(GZ) AS GZ FROM EMP GROUP BY GZ) WHERE RN BETWEEN (SELECT COUNT(1) - 4 FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)) AND (SELECT COUNT(1) FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)))

你这个确定没错误？？ ROW_NUMBER() OVER() 里面可以不写order by？

回复赞

restbely 2013-05-02

rank型 SELECT GZ FROM (SELECT ROW_NUMBER() OVER() AS RN, MAX(GZ) OVER(PARTITION BY GZ) AS GZ FROM EMP) WHERE RN BETWEEN (SELECT COUNT(1) - 4 FROM EMP) AND (SELECT COUNT(1) FROM EMP) dense_rank型 SELECT GZ FROM EMP WHERE GZ IN (SELECT GZ FROM (SELECT ROW_NUMBER() OVER() AS RN, MAX(GZ) AS GZ FROM EMP GROUP BY GZ) WHERE RN BETWEEN (SELECT COUNT(1) - 4 FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)) AND (SELECT COUNT(1) FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)))

回复赞

super007007007 2013-04-29

select sal ,sal_max ,rn from (select sal ,rownum rn ,cal_cnt ,sal_max from (select sal ,sum(1) over(partition by 'a') cal_cnt ,max(sal) over(partition by 'a') sal_max from emp ) bb ) where rn < cal_cnt - 5

回复赞

五更琉璃 2013-04-28

select * from (select (select count(*) from emp tt where tt.sal > t.sal) as cou, t.sal from emp t) where cou < 5 and rownum <= 5

回复赞

hh7yx 2013-04-28

引用 10 楼 NIan_jun 的回复:


select *
  from emp
 start with sal = (select max(sal) from emp)
CONNECT BY PRIOR sal > sal　and rownum <6

不过有两个sal 相等的没取到

严格来说，你这个sql是错的。

回复赞

hh7yx 2013-04-28

引用 11 楼 buryMyLove 的回复:

然后也可以用rank over()吧

啥都可以用，只要sql里面不出现order by这几个字

回复赞

hh7yx 2013-04-28

引用 9 楼 buryMyLove 的回复:

不用order by 用group by 行么，b字段是工资 select * from temp t1 where t1.b >= (select max(b) from (select t.b from temp t group by t.b) where rownum <= (select count(*) from temp) - 4);

嗯，答案是正确的，不过还可以简化

回复赞

buryMyLove 2013-04-28

然后也可以用rank over()吧

回复赞

NIan_jun 2013-04-28


select *
  from emp
 start with sal = (select max(sal) from emp)
CONNECT BY PRIOR sal > sal　and rownum <6

不过有两个sal 相等的没取到

回复赞

buryMyLove 2013-04-28

不用order by 用group by 行么，b字段是工资 select * from temp t1 where t1.b >= (select max(b) from (select t.b from temp t group by t.b) where rownum <= (select count(*) from temp) - 4);

回复赞

hh7yx 2013-04-28

引用 7 楼 liaimin12345 的回复: