Oracle_SQL常见问题（TOPM~N）_02

最新推荐文章于 2024-01-09 13:28:38 发布

原创最新推荐文章于 2024-01-09 13:28:38 发布 · 930 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql

Oracle 专栏收录该内容

15 篇文章

订阅专栏

本文介绍使用Oracle数据库实现M-N查询的方法，包括利用ROWNUM伪列、MINUS运算及ROW_NUMBER()、DENSE_RANK()、RANK()等窗口函数的不同方式，特别关注于如何高效地获取指定排名区间的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

求M-N问题

1. rownum伪列

对rownum进行提前的判断，缩小了检索的范围

SELECT  t2.*
FROM   (SELECTrownum lvl, t1.*
        FROM  (SELECT emp.empno, ename, nvl(sal, 0)
                FROM   emp
                ORDER  BY nvl(sal, 0) DESC) t1
        WHERE rownum <= 4) t2
WHERE  t2.lvl >= 2

2. rownum伪列

SELECT t2.*
FROM   (SELECTrownum lvl, t1.*
        FROM  (SELECT emp.empno, ename,sal
                FROM   emp
                ORDER  BY nvl(sal, 0) DESC) t1) t2
WHERE  t2.lvl BETWEEN 2 AND 4

3.minus

SELECTrownum, t1.*
FROM   (SELECT * FROM emp ORDERBY nvl(sal, 0) DESC) t1
WHERE  rownum <= 4
MINUS
SELECTrownum, t1.*
FROM   (SELECT * FROM emp ORDERBY nvl(sal, 0) DESC) t1
WHERE  rownum = 1

当然我们可以利用集合的差运算，注意，rownum不能用于等于，用于等于的时候只能有一种情况

那就是当rownum = 1的时候

4利用oracle自带的函数来进行求解

row_number() over( ) 按照行的序号来显示

SELECT t2.*
FROM   (SELECT t1.empno,
               t1.ename,
               t1.sal,
               row_number() over(ORDER BY t1.sal DESC) grade
        FROM  emp t1) t2
WHERE  t2.grade BETWEEN 2 AND 4

dense_rank( ) over( )

SELECT t2.*
FROM   (SELECT t1.empno,
               t1.ename,
               t1.sal,
               dense_rank() over(ORDER BY t1.sal DESC) grade
        FROM  emp t1) t2
WHERE  t2.grade BETWEEN 2 AND 4

rank( )over( )

SELECT t2.*
FROM   (SELECT t1.deptno,
               t1.empno,
               t1.ename,
               t1.sal,
               rank() over(ORDER BY t1.sal DESC) grade
        FROM  emp t1) t2
WHERE  t2.grade BETWEEN 2 AND 4

根据需要来选择函数

Q:求各个部门的工资在2-4名的员工。（PARTITION BY）

SELECT t2.*
FROM   (SELECT t1.deptno,
               t1.empno,
               t1.ename,
               t1.sal,
               rank() over(PARTITION BY deptno ORDER BY t1.sal DESC) grade
        FROM  emp t1) t2
WHERE  t2.grade BETWEEN 2 AND 4