来写个sql 玩玩。。

[u010412956]

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

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

[u010412956]

其实不难、的的

[japenfese]

select /*+ index_desc(emp,ind_emp_sal)*/ from emp where rownum <= 5 ind_emp_sal是建立在emp(sal)上的索引.

[japenfese]

错了 少了东西... select /*+ index_desc(emp,ind_emp_sal)*/ * from emp where rownum <= 5

[u010412956]

引用 3 楼 liaimin12345 的回复:

select /*+ index_desc(emp,ind_emp_sal)*/ from emp where rownum <= 5 ind_emp_sal是建立在emp(sal)上的索引.

1.题目是一条sql，你这样要多建1个索引，不符合要求 2.你确定你这样做 能达到效果？测试了么。。

[new__class]

留个脚印，五一回来后看结果

[japenfese]

确实没有测，我是想让他根据索引来的排序来取值，因为索引是已经排序好的，这样我们就不用排序了. 撸主有什么好办法吗，求共享

[u010412956]

引用 7 楼 liaimin12345 的回复:

确实没有测，我是想让他根据索引来的排序来取值，因为索引是已经排序好的，这样我们就不用排序了. 撸主有什么好办法吗，求共享

看到答案你会知道 这个sql如此简单。。。 仔细想想应该不难。我相信有不少人能贴出自己的sql的

[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);

[NIan_jun]

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

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

[buryMyLove]

然后也可以用rank over()吧

[u010412956]

引用 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);

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

[u010412956]

引用 11 楼 buryMyLove 的回复:

然后也可以用rank over()吧

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

[u010412956]

引用 10 楼 NIan_jun 的回复:

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

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

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

[五更琉璃]

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

[super007007007]

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

[小海葵1]

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)))

[u010412956]

引用 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？

[ICE-word]

引用 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) 这个值就不行了！

[Iam太陽神]

引用 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

[绝望依然]

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