FYI: 一条查询语句: EMP有3个字段 EMANE,DEPTNO,SAL求查询每个部门薪水前十名的纪录 用一条SQL语句 (两种方法) -- 1st way: SELECT * FROM (SELECT ENAME, DEPTNO, SAL, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) RK FROM EMP) WHERE RK <= 10;-- 2nd way: SELECT * FROM EMP E1 WHERE ( SELECT COUNT(1) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL < E2.SAL ) < 10;
如果以人数计算: 方法1: SELECT * FROM (SELECT RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK, EMP.* FROM EMP) WHERE RK < 3; 方法2: SELECT * FROM EMP E1 WHERE (SELECT COUNT(1) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL < E2.SAL) < 2 order by deptno,sal desc;如果以名次计算: 方法1: SELECT * FROM (SELECT DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK, EMP.* FROM EMP) WHERE RK < 3;
方法2: SELECT * FROM EMP E1 WHERE (SELECT COUNT(1) FROM (SELECT DISTINCT SAL,DEPTNO FROM EMP) E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL < E2.SAL) < 2 order by deptno,sal desc;
按楼上朋友的改了一下,我也是刚学,感谢楼上 :-)-- 1st way: SELECT * FROM (SELECT ENAME, DEPTNO, SAL, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK FROM SCOTT.EMP) WHERE RK <= 2;-- 2nd way: SELECT * FROM SCOTT.EMP E1 WHERE ( SELECT COUNT(1) FROM SCOTT.EMP E2 WHERE E1.DEPTNO = E2.DEPTNO AND E1.SAL < E2.SAL ) <2 ORDER BY DEPTNO,SAL DESC
-- 1st way:
SELECT *
FROM (SELECT ENAME,
DEPTNO,
SAL,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) RK
FROM EMP)
WHERE RK <= 10;-- 2nd way:
SELECT *
FROM EMP E1
WHERE (
SELECT COUNT(1)
FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL < E2.SAL
) < 10;
方法1:
SELECT *
FROM (SELECT RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK, EMP.*
FROM EMP)
WHERE RK < 3;
方法2:
SELECT *
FROM EMP E1
WHERE (SELECT COUNT(1)
FROM EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL < E2.SAL) < 2
order by deptno,sal desc;如果以名次计算:
方法1:
SELECT *
FROM (SELECT DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK, EMP.*
FROM EMP)
WHERE RK < 3;
方法2:
SELECT *
FROM EMP E1
WHERE (SELECT COUNT(1)
FROM (SELECT DISTINCT SAL,DEPTNO FROM EMP) E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL < E2.SAL) < 2
order by deptno,sal desc;
SELECT *
FROM (SELECT ENAME,
DEPTNO,
SAL,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RK
FROM SCOTT.EMP)
WHERE RK <= 2;-- 2nd way:
SELECT *
FROM SCOTT.EMP E1
WHERE (
SELECT COUNT(1)
FROM SCOTT.EMP E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E1.SAL < E2.SAL
) <2 ORDER BY DEPTNO,SAL DESC