给你个例子: SELECT * FROM (SELECT EMPNO, ENAME, SAL, DEPTNO, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL) R1, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R2 FROM SCOTT.EMP) WHERE R1 <= 2 OR R2 <= 2 ORDER BY DEPTNO, SAL;
-- JUST TRY IT .. SELECT * FROM EMP E1 WHERE ( SELECT COUNT(1) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL < E2.SAL ) < 5 OR ( SELECT COUNT(1) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL > E2.SAL ) < 5;
select EmployeeID,salary_money,Dept from ( select a.*,dense_rank(partition by dept order_by salary_monet) asc_money, dense_rank(partition by dept order_by salary_monet desc) desc_money from salary a) where asc_money<=5 or desc_money<=5desc_money是各部门工资最高的五名,ASC是最低的五名如果有并列的就全取出来如果不考虑并列的,只取5个人,就用row_number替换dense_rank
from (select id, employeeid, salary_money, dept, row_number() over(partition by dept order by salary_money) rn, row_number() over(partition by dept order by salary_money desc) rw from tab_salay ) where rn<=5 or rw<=5
SELECT *
FROM (SELECT EMPNO,
ENAME,
SAL,
DEPTNO,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL) R1,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R2
FROM SCOTT.EMP)
WHERE R1 <= 2
OR R2 <= 2
ORDER BY DEPTNO, SAL;
SELECT *
FROM EMP E1
WHERE (
SELECT COUNT(1)
FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL < E2.SAL
) < 5
OR (
SELECT COUNT(1)
FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL > E2.SAL
) < 5;
from (
select a.*,dense_rank(partition by dept order_by salary_monet) asc_money,
dense_rank(partition by dept order_by salary_monet desc) desc_money
from salary a)
where asc_money<=5 or desc_money<=5desc_money是各部门工资最高的五名,ASC是最低的五名如果有并列的就全取出来如果不考虑并列的,只取5个人,就用row_number替换dense_rank
还是3楼正解
create table TAB_SALAY
(
ID NUMBER,
EMPLOYEEID NUMBER,
SALARY_MONEY NUMBER,
DEPT NUMBER
);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (1, 1, 100, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (2, 2, 100, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (3, 3, 100, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (4, 4, 200, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (5, 5, 400, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (6, 6, 300, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (7, 7, 200, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (8, 8, 200, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (9, 9, 1400, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (10, 10, 1500, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (11, 11, 1300, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (12, 12, 1200, 1);
insert into TAB_SALAY (ID, EMPLOYEEID, SALARY_MONEY, DEPT)
values (13, 13, 1200, 1);
commit;
select id,
employeeid,
salary_money,
dept
from
(select id,
employeeid,
salary_money,
dept,
row_number() over(partition by dept order by salary_money) rn,
row_number() over(partition by dept order by salary_money desc) rw
from tab_salay ) where rn<=5 or rw<=5