ORACLE_高级查询（2）_分页查询_子查询的练习_Unit_4;

本文链接：https://blog.youkuaiyun.com/qq_38053395/article/details/82972732

本文深入探讨SQL分页查询技巧，包括rownum和rowid的使用，解决Top-N问题，以及如何通过子查询优化查询结果。同时，文章提供了多个子查询实践案例，如查询工资范围内的员工信息，找出工资不超过3000的人数最多的部门，及管理员工人数最多的人的名字等。

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

Topic 1 : 分页查询

SQL> --伪列：不属于某一张具体的表
SQL> --rownum   s数据的逻辑地址
SQL> --rowid    数据的为物理地址
SQL> select e.empno,e.ename from emp e;

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- --------------------
      7900 JAMES
      7902 FORD
      7934 MILLER

已选择14行。

SQL> set linesize 180;
SQL> set pagesize 180;
SQL> select e.empno,e.ename from emp e;

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

已选择14行。

SQL> --上面那句话用到了给表取别名的方法  就是在表的后面加上空格和他的别名就好了
SQL> select rownum,rowid,e.ename from emp e;

    ROWNUM ROWID              ENAME
---------- ------------------ --------------------
         1 AAAR3sAAEAAAACXAAA SMITH
         2 AAAR3sAAEAAAACXAAB ALLEN
         3 AAAR3sAAEAAAACXAAC WARD
         4 AAAR3sAAEAAAACXAAD JONES
         5 AAAR3sAAEAAAACXAAE MARTIN
         6 AAAR3sAAEAAAACXAAF BLAKE
         7 AAAR3sAAEAAAACXAAG CLARK
         8 AAAR3sAAEAAAACXAAH SCOTT
         9 AAAR3sAAEAAAACXAAI KING
        10 AAAR3sAAEAAAACXAAJ TURNER
        11 AAAR3sAAEAAAACXAAK ADAMS
        12 AAAR3sAAEAAAACXAAL JAMES
        13 AAAR3sAAEAAAACXAAM FORD
        14 AAAR3sAAEAAAACXAAN MILLER

已选择14行。

SQL> --逻辑地址从一开始递增；
SQL> --我们的伪列是不能加e. 因为伪列不属于某一张具体的表；
SQL> --rownum  一但生成是不会呗改变的
SQL> select rownum,rowid,e.ename from emp e order by sal;

    ROWNUM ROWID              ENAME
---------- ------------------ --------------------
         1 AAAR3sAAEAAAACXAAA SMITH
        12 AAAR3sAAEAAAACXAAL JAMES
        11 AAAR3sAAEAAAACXAAK ADAMS
         3 AAAR3sAAEAAAACXAAC WARD
         5 AAAR3sAAEAAAACXAAE MARTIN
        14 AAAR3sAAEAAAACXAAN MILLER
        10 AAAR3sAAEAAAACXAAJ TURNER
         2 AAAR3sAAEAAAACXAAB ALLEN
         7 AAAR3sAAEAAAACXAAG CLARK
         6 AAAR3sAAEAAAACXAAF BLAKE
         4 AAAR3sAAEAAAACXAAD JONES
         8 AAAR3sAAEAAAACXAAH SCOTT
        13 AAAR3sAAEAAAACXAAM FORD
         9 AAAR3sAAEAAAACXAAI KING

已选择14行。

SQL> --我们这里的rowid并没有按顺序排列上面说的就是原理；
SQL> Top-N  问题
SP2-0734: 未知的命令开头 "Top-N  问..." - 忽略了剩余的行。
SQL> --Top-N  问题
SQL> --例题： 显示工资最高的前三条数据：
SQL> select rownum,ename,sal from emp where rownum>3 order by sal desc;

未选定行

SQL> select rownum,ename,sal from emp where rownum <= 3 order by sal desc;

    ROWNUM ENAME                       SAL
---------- -------------------- ----------
         2 ALLEN                      1600
         3 WARD                       1250
         1 SMITH                       800

SQL> --这样查询是错误的原因就是我们的rownum已经在我们第一次查询的时候就已经生成了而且没有按照工资的降序来生成；
SQL> --我们可以使用子查询让子查询按照工资降序来生成rownum  这样我们就可以使用rownum<=3来限定了；
SQL> --这个就是我们说的那个子查询要排序的那个特例；
SQL> select empno,ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum <= 3;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7839 KING                       5000
      7788 SCOTT                      3000
      7902 FORD                       3000

SQL> --查询的时候查询子查询生成的那个表
SQL> --rownum的两个特性  1.在数据初始化的时候自动生成  且不会改变了；   2.rownum只能使用小于或小于等于不能使用大于或大于等于；
SQL> -- 例题： 我们查询工资在第七和第九之间的员工信息；
SQL> select ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum >= 7 and rowum <= 9;
where rownum >= 7 and rowum <= 9
                      *
第 3 行出现错误:
ORA-00904: "ROWUM": 标识符无效


SQL> select ename,sal
  2  from (select * from emp order by sal desc)
  3  from (select * from emp order by sal desc)
  4
SQL> select ename,sal
  2  from (select * from emp order by sal desc)
  3  where rownum >= 7 and rownum <= 9;

未选定行

SQL> --这个就是犯了rownum第二条错误的

SQL>
SQL> select ename,sal from
  2  (select rownum r,e.* from
  3  (select * from emp order by sal desc) e
  4  )
  5  where r>=7 and r<=9;

ENAME                       SAL
-------------------- ----------
ALLEN                      1600
TURNER                     1500
MILLER                     1300
--我们这里使用的思想就是使用两次子查询  第一次子查询把这个表按照工资的降序排列
--第二次子查询查询第一次降序生成的表  把它的rownum（重命名为r）和*拿出来合成一张新的表这样这张新
--的表就有 r 列和员工信息的所有列  这样我们就可以用r使用>= 号了 这样原本的难题就迎刃而解了；
SQL> --表的别名可以被自身表所使用但是列的别名不能被自身列所使用；
SQL> --比如我们把上面的rownum更换成他的b别名 r
SQL> ed
已写入 file afiedt.buf

  1  select r,
  2  ename,sal from
  3  (select rownum r,e.* from
  4  (select * from emp order by sal desc) e
  5  where r <= 9
  6  )
  7* where r>=7
SQL> /
where r <= 9
      *
第 5 行出现错误:
ORA-00904: "R": 标识符无效


SQL> --上面会报r标识符无效但是他不会表e这个别名无效；

SQL> set pagesize 180;
SQL> set line size 180;
SP2-0268: linesize 选项的编号无效
SQL> set linesize 180;
SQL> ed
已写入 file afiedt.buf

  1  select * from (select rownum r,e.* from
  2  (select * from emp order by sal desc) e
  3  where rownum <= 2*3)
  4* where r >= (2-1)*3+1
SQL> /

         R      EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         4       7566 JONES                MANAGER                  7839 02-4月 -81           2975                20
         5       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         6       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10

sqlServer 分页：

top就是取排名的前几个按照id的顺序进行分页；

他里面没有rownum这个关键字

MySql的分页查询：

MySql ：的第一条数据是0；

而前两个是1；

这limit第一个参数是从第几条数据开始；第二个参数是从一共延续几条；

页面大小是pageSize；

Topic 2 : 子查询练习

--查询出工资不超过3000的人数最多的部门名称
--对于第四行我们本来只要按照部门分组就好了  但是我们看到select的后面有d.name
--我们知道不在组函数中的列必须在group by的后面  所以有了d.name  但是也要考虑他的实际意义；

select d.deptno,d.dname from dept d,emp e
where d.deptno = e.deptno
and e.sal < 3000
group by d.deptno,d.dname
having count(*) = (select max(count(*)) from emp
where sal <=3000
group by deptno);


--查询每种工作的最低工资  以及领取该工资的员工姓名

--从字面上理解我们会这样写
select ename,min(sal) from emp
group by deptno;
--这样我们发现运行不了因为  ename没有在组函数和group by其中之一
--我们改进
select ename,min(sal) from emp
group by deptno,ename;
--然后我们会发现这样我们只是按照名字分组了  查出来的就是每个人的名字和工资而已
--我们于是想到分开来查员工姓名和每种工作的最低工资
select ename from emp;
select min(sal) from emp group by deptno;
--我们于是就想把 ename查出来  然后把第二行看作一个表来查
select ename,t.minsal from emp,(select min(sal) minsal from emp group by deptno) t;
--我们发现还是不对  因为没有进行多表链接  查出来的只是他们的笛卡儿积；
select ename,t.minsal from emp,(select min(sal) minsal,job from emp group by deptno,job) t
where emp.sal = t.minsal and emp.job = t.job;

--3. 查询出管理员工人数最多的人的名字  和他管理人的名字
我们这题答案见图片；


create table mystu1 (
stuno number,
stuname varchar2(20));