子查询

子查询

子查询就是把一个查询的结果集，作为另外一个查询的条件使用

• 子查询作为条件
• 子查询当做表使用

子查询作为条件

-- 查出跟SCOTT在同一个部门工作的所有员工的信息
SELECT *
FROM EMP
WHERE DEPTNO = (
         SELECT E.DEPTNO
         FROM EMP E
         WHERE E.ENAME='SCOTT');  --先查询出SCOTT所在的部门

-- 查询出工资高于全公司平均工资的员工的信息
SELECT *
FROM EMP 
WHERE SAL > (SELECT AVG(SAL) FROM EMP E) --先查询出全公司的平均工资

子查询当做表使用

• 伪列

1. ROWNUM 逻辑上存在序号自动增加的列
2. ROWID 物理地址，表中的数据，存在数据库磁盘中的位置

SELECT * ,ROWNUM  ,ROWID 
FROM EMP
WHERE ROWNUM <= 5 ;

建TESTA表测试

CREATE TABLE TESTA(A_ID NUMBER);

SELECT *,ROWID 
FROM TESTA;

--找出TESTA表中的唯一记录
--方法一 DISTINCT
SELECT DISTINCT A_ID 
FROM TESTA; --只能找出唯一值，但是不能找出重复的记录
--方法二 GROUP BY
SELECT A_ID
FROM TESTA
GROUP BY A_ID;--可以找出唯一值，但是找重复记录的时候，会包含唯一的值
--方法三 ROWID
SELECT A_ID
FROM TESTA
WHERE ROWID IN (
      SELECT MAX(ROWID) 
      FROM TESTA
      GROUP BY A_ID); -- MAX(ROWID)为最新的数据,MIN(ROWID)为最原始的数据

--找出TESTA表中的重复记录，不包含唯一值
--使用方法二 
SELECT A_ID
FROM TESTA
GROUP BY A_ID
HAVING COUNT(*)>1;
--使用方法三
SELECT A_ID
FROM TESTA
WHERE ROWID NOT IN (
      SELECT MAX(ROWID)
      FROM TESTA
      GROUP BY A_ID);

 -- 查询出工资排名 第5~10的员工
 SELECT F.*
 FROM (SELECT E.*,ROWNUM RN   --给排序过后的工资进行ROWNUM伪列
       FROM (SELECT *    
             FROM EMP
             ORDER BY SAL DESC) E  --先将工资由高到低进行排序
             ) F
 WHERE F.RN BETWEEN 5 AND 10;  

子查询什么时候使用

1. 当条件不明确的时候，子查询作为条件
2. 当条件不满足，我们可以通过子查询查出我们想要的结果集，把子查询当做表使用

子查询返回结果的几种形式

• 子查询返回单行

--查出最高工资的员工的信息
SELECT *
FROM EMP
WHERE SAL = ( SELECT MAX(SAL) 
              FROM EMP); 

• 子查询返回多行

--找出员工表中所有的领导 （MGR）
SELECT * 
FROM EMP 
WHERE EMPNO IN (SELECT MGR 
                FROM EMP );

• 子查询返回多行多列（类似表）

 -- 查询跟SCOTT 岗位相同，部门也相同的所有员工
 --方法一
SELECT *
FROM EMP
WHERE DEPTNO =(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT') --SCOTT所在的部门
      AND JOB =(SELECT JOB FROM EMP WHERE ENAME='SCOTT'); --SCOTT的岗位
 --方法二
 SELECT * 
 FROM EMP E
 WHERE (E.DEPTNO ,E.JOB) = (SELECT DEPTNO ,JOB 
                            FROM EMP 
                            WHERE ENAME = 'SCOTT');