select /*+ index_desc(emp,ind_emp_sal)*/ from emp where rownum <= 5 ind_emp_sal是建立在emp(sal)上的索引.
错了 少了东西... select /*+ index_desc(emp,ind_emp_sal)*/ * from emp where rownum <= 5
1.题目是一条sql,你这样要多建1个索引,不符合要求2.你确定你这样做 能达到效果?测试了么。。
看到答案你会知道 这个sql如此简单 仔细想想应该不难。我相信有不少人能贴出自己的sql的
不用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);
select * from emp start with sal = (select max(sal) from emp) CONNECT BY PRIOR sal > sal and rownum <6 不过有两个sal 相等的 没取到
啥都可以用,只要sql里面不出现order by这几个字
严格来说,你这个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
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
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?
嗯,答案是正确的,不过还可以简化不行吧!如果max(b) 小于 (select count(*) from temp) 这个值就不行了!
ind_emp_sal是建立在emp(sal)上的索引.
1.题目是一条sql,你这样要多建1个索引,不符合要求2.你确定你这样做 能达到效果?测试了么。。
看到答案你会知道 这个sql如此简单
仔细想想应该不难。我相信有不少人能贴出自己的sql的
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);
select *
from emp
start with sal = (select max(sal) from emp)
CONNECT BY PRIOR sal > sal and rownum <6
不过有两个sal 相等的 没取到
啥都可以用,只要sql里面不出现order by这几个字
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
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
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)))
嗯,答案是正确的,不过还可以简化不行吧!如果max(b) 小于 (select count(*) from temp) 这个值就不行了!
Oracle11g