或者用分析函数 select * from(select depid,empid,salary,row_number() over(partition by deptid order by salary desc) top3 from table) where top3<=3
楼上的 top3在access中可用,在oracle中不可用 应该用exists 加group by 语句
select * from (SELECT * FROM EMTABLE ORDER BY SALARY) where rownum<=3 order by rownum desc我也是新手,不知道对不对啊
select * from (select * from emp order by SALARY desc) where rownum<=3或者用row_number分析函数处理
暂时只想到用游标把部门号全取到,然后对每个部门求前三(top n),期待还有更好的办法
woodfans(woodfans) select * from(select depid,empid,salary,row_number() over(partition by deptid order by salary desc) top3 from table) where top3<=3正确。分析函数很有用。
select * from emp e1 where exists( select * from (select * from emp e2 where e2.deptno=e1.deptno order by sal desc) where rownum<=3); 这个语句为什么总是提示错误呢,说e1不存在,为什么呢?
SELECT * FROM ( SELECT e.*, DENSE_RANK() over( ORDER BY salary) tid FROM hr.employees e ) a WHERE a.tid<4
楼上错误,注意是要按部门。加partition by deptno才正确: 1 select * 2 from ( select ename,deptno,sal, 3 dense_rank() over (partition by deptno order by sal desc) rid 4 from emp 5 ) 6* where rid <= 3 SQL> /ENAME DEPTNO SAL RID ---------- ---------- ---------- ---------- KING 10 5000 1 CLARK 10 2450 2 MILLER 10 1300 3 SCOTT 20 3000 1 FORD 20 3000 1 JONES 20 2975 2 SMITH 20 1600 3 BLAKE 30 2850 1 ALLEN 30 1600 2 TURNER 30 1500 3已选择10行。 ====================================================== 2楼的不够正确:(有相等薪水额问题) 1 select * 2 from ( select empno,ename,deptno,sal, 3 row_number() over (partition by deptno order by sal desc) top3 4 from emp) 5* where top3 <= 3 SQL> / EMPNO ENAME DEPTNO SAL TOP3 ---------- ---------- ---------- ---------- ---------- 7839 KING 10 5000 1 7782 CLARK 10 2450 2 7934 MILLER 10 1300 3 7788 SCOTT 20 3000 1 7902 FORD 20 3000 2 7566 JONES 20 2975 3 7698 BLAKE 30 2850 1 7499 ALLEN 30 1600 2 7844 TURNER 30 1500 3已选择9行。
select * from(select depid,empid,salary,row_number() over(partition by deptid order by salary desc) top3 from table) where top3<=3
应该用exists 加group by 语句
select * from (SELECT * FROM EMTABLE ORDER BY SALARY) where rownum<=3 order by rownum desc我也是新手,不知道对不对啊
select * from(select depid,empid,salary,row_number() over(partition by deptid order by salary desc) top3 from table) where top3<=3正确。分析函数很有用。
where exists(
select *
from
(select *
from emp e2
where e2.deptno=e1.deptno
order by sal desc)
where rownum<=3);
这个语句为什么总是提示错误呢,说e1不存在,为什么呢?
(
SELECT e.*, DENSE_RANK() over( ORDER BY salary) tid
FROM hr.employees e
) a
WHERE a.tid<4
2 from ( select ename,deptno,sal,
3 dense_rank() over (partition by deptno order by sal desc) rid
4 from emp
5 )
6* where rid <= 3
SQL> /ENAME DEPTNO SAL RID
---------- ---------- ---------- ----------
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
SCOTT 20 3000 1
FORD 20 3000 1
JONES 20 2975 2
SMITH 20 1600 3
BLAKE 30 2850 1
ALLEN 30 1600 2
TURNER 30 1500 3已选择10行。
======================================================
2楼的不够正确:(有相等薪水额问题) 1 select *
2 from ( select empno,ename,deptno,sal,
3 row_number() over (partition by deptno order by sal desc) top3
4 from emp)
5* where top3 <= 3
SQL> / EMPNO ENAME DEPTNO SAL TOP3
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 MILLER 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 2
7566 JONES 20 2975 3
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3已选择9行。