# 第 13 节 综合案例：复杂查询

1. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数，平均工资。

* 确定要使用的数据表：
  * emp表：可以获取员工信息；
  * dept表：部门名称；
  * emp表：员工的统计信息；
* 确定已知的关联字段：
  * 雇员和部门：`emp.deptno=deptno.deptno`；
* \*\*第一步：\*\*首先查询出SCOTT的职位；

```sql
SELECT job FROM emp WHERE ename='SCOTT';
```

* \*\*第二步：\*\*根据SCOTT的职位找到具备此职位信息的员工；

```sql
SELECT e.empno,e.ename,e.job
FROM emp e
WHERE e.job=(
    SELECT job FROM emp WHERE ename='SCOTT')
    AND e.ename<>'SCOTT';
```

* \*\*第三步：\*\*要查询出该支援对应的部门名称；

```sql
SELECT e.empno,e.ename,e.job,d.dname
FROM emp e,dept d
WHERE e.job=(
    SELECT job FROM emp WHERE ename='SCOTT')
    AND e.ename<>'SCOTT'
    AND e.deptno=d.deptno;
```

* \*\*第四步：\*\*需要统计出部门的人数和平均工资；
  * GROUP BY使用限制：
    * 如果一个查询语句之中没有GROUP BY，SELECT子句只能够出现统计函数；
    * 如果查询语句之中存在有GROUP BY，则SELECT子句可以出现分组字段和统计函数；
    * 统计函数允许嵌套，嵌套之后的SELECT子句不允许出现任何字段，包括分组字段；
* 此时需要进行分组统计，但是已有的查询明显是不能够进行直接的分组操作的，那么就在FROM子句中编写子查询。

```sql
SELECT e.empno,e.ename,e.job,d.dname,temp.count,temp.avg
FROM emp e,dept d,(
    SELECT deptno,COUNT(empno) count,AVG(sal) avg
    FROM emp
    GROUP BY deptno) temp
WHERE e.job=(
    SELECT job FROM emp WHERE ename='SCOTT')
    AND e.ename<>'SCOTT'
    AND e.deptno=d.deptno
    AND e.deptno=temp.deptno;
```

2. 列出薪金高于在部门30工作的所有员工的薪金以及高于公司平均工资的员工姓名和薪金、部门名称、部门平均工资、个人工资等级。

* 确定要使用的数据表：
  * emp表：员工姓名和薪金；
  * dept表：部门名称；
  * emp表：部门平均工资；
  * salgrade表：个人工资等级；
* 确定已知的关联字段：
  * 雇员和部门：`emp.deptno=deptno.deptno`；
  * 雇员与工资等级：`emp.sal BETWEEN salgrade.losal AND salgrade.hisal`；
* \*\*第一步：\*\*查询所有在30部门工作的员工工资

```sql
SELECT sal FROM emp WHERE deptno=30;
```

* \*\*第二步：\*\*查询出所有大于30部门员工工资的雇员信息，一定使用`>ALL`；

```sql
SELECT e.ename,e.sal
FROM emp e
WHERE e.sal>ALL(
  SELECT sal FROM emp WHERE deptno=30);
```

* \*\*第三步：\*\*高于公司平均工资

```sql
SELECT e.ename,e.sal
FROM emp e
WHERE e.sal>ALL(
    SELECT sal FROM emp WHERE deptno=30)
    AND e.sal>(SELECT AVG(sal) FROM emp);
```

* \*\*第四步：\*\*引入dept表查询出部门名称

```sql
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.sal>ALL(
    SELECT sal FROM emp WHERE deptno=30)
    AND e.sal>(SELECT AVG(sal) FROM emp)
    AND e.deptno=d.deptno;
```

* \*\*第五步：\*\*获取部门平均工资

```sql
SELECT e.ename,e.sal,d.dname,temp.avg
FROM emp e,dept d,(
    SELECT deptno,AVG(sal) avg
    FROM emp
    GROUP BY deptno) temp
WHERE e.sal>ALL(
    SELECT sal FROM emp WHERE deptno=30)
    AND e.sal>(SELECT AVG(sal) FROM emp)
    AND e.deptno=d.deptno
    AND e.deptno=temp.deptno;
```

* \*\*第六步：\*\*工资等级直接引入salgrade表

```sql
SELECT e.ename,e.sal,d.dname,temp.avg,s.grade
FROM emp e,dept d,(
    SELECT deptno,AVG(sal) avg
    FROM emp
    GROUP BY deptno) temp,salgrade s
WHERE e.sal>ALL(
    SELECT sal FROM emp WHERE deptno=30)
    AND e.sal>(SELECT AVG(sal) FROM emp)
    AND e.deptno=d.deptno
    AND e.deptno=temp.deptno
    AND e.sal BETWEEN s.losal AND s.hisal;
```

3. 查询出所有雇员编号、姓名、职位、薪金、此职位的人数、此职位的人数、领导姓名、领导职位、领导所在职位的人数、雇员部门、雇员部门人数、领导部门、领导所在部门人数、雇员工资等级以及此等级的平均工资。

* 确定要使用的数据表：
  * emp表：员工信息；
  * emp表：领导信息；
  * emp表：领导统计信息；
  * emp表：部门统计信息；
  * emp表：工资等级统计信息；
  * dept表：部门信息；
  * salgrade表：工资等级信息；
* 确定已知的关联字段：
  * 雇员与部门：`emp.deptno=dept.deptno`；
  * 雇员与领导：`emp.mgr=memp.empno`；
  * 雇员与工作：`emp.sal BETWEEN salgrade.losal AND salgrade.hisal`；
* \*\*第一步：\*\*查询出所有雇员编号、姓名、职位、薪金

```sql
SELECT e.empno,e.ename,e.job,e.sal
FROM emp e;
```

* \*\*第二步：\*\*查询出员工所对应职位的人数

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp
WHERE e.job=empjobtemp.job;
```

* \*\*第三步：\*\*领导姓名、领导职位

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,m.ename,m.job
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+);
```

* \*\*第四步：\*\*领导所在职位人数，应该按照领导的职位分组：

  * 首先要求筛选出所有的领导：

  ```sql
  SELECT DISTINCT mgr FROM emp;
  ```

  * 领导的职位要根据以上的返回结果根据job分组：

  ```sql
  SELECT job
  FROM emp
  WHERE empno IN (
      SELECT DISTINCT mgr FROM emp);
  ```

  * 随后以上面的职位信息进行分组的数据的筛选：

  ```sql
  SELECT job,COUNT(*)
  FROM emp
  WHERE job IN (
      SELECT job
      FROM emp
      WHERE empno IN (
          SELECT DISTINCT mgr FROM emp))
  GROUP BY job;
  ```

  * 需要将以上的查询安放到之前的查询之中：

  ```sql
  SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,m.ename,m.job,mgrjobtemp.count
  FROM emp e,(
      SELECT job,COUNT(*) count
      FROM emp
      GROUP BY job) empjobtemp,emp m,(
      SELECT job,COUNT(*) count
      FROM emp
      WHERE job IN (
          SELECT job
          FROM emp
          WHERE empno IN (
              SELECT DISTINCT mgr FROM emp))
      GROUP BY job) mgrjobtemp
  WHERE e.job=empjobtemp.job
      AND e.mgr=m.empno(+)
      AND m.job=mgrjobtemp.job;
  ```
* \*\*第五步：\*\*要获取雇员的部门信息则引入部门表

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,m.ename,m.job,mgrjobtemp.count,d.dname
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m,(
    SELECT job,COUNT(*) count
    FROM emp
    WHERE job IN (
        SELECT job
        FROM emp
        WHERE empno IN (
            SELECT DISTINCT mgr FROM emp))
    GROUP BY job) mgrjobtemp,dept d
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+)
    AND m.job=mgrjobtemp.job
    AND e.deptno=d.deptno;
```

* \*\*第六步：\*\*雇员部门人数

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,m.ename,m.job,mgrjobtemp.count,d.dname,empdepttemp.count
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m,(
    SELECT job,COUNT(*) count
    FROM emp
    WHERE job IN (
        SELECT job
        FROM emp
        WHERE empno IN (
            SELECT DISTINCT mgr FROM emp))
    GROUP BY job) mgrjobtemp,dept d,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    GROUP BY deptno) empdepttemp
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+)
    AND m.job=mgrjobtemp.job
    AND e.deptno=d.deptno
    AND e.deptno=empdepttemp.dno;
```

* \*\*第七步：\*\*要求查询出领导对应的部门信息

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,m.ename,m.job,mgrjobtemp.count,d.dname,empdepttemp.count,d2.dname
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m,(
    SELECT job,COUNT(*) count
    FROM emp
    WHERE job IN (
        SELECT job
        FROM emp
        WHERE empno IN (
            SELECT DISTINCT mgr FROM emp))
    GROUP BY job) mgrjobtemp,dept d,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    GROUP BY deptno) empdepttemp,dept d2
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+)
    AND m.job=mgrjobtemp.job
    AND e.deptno=d.deptno
    AND e.deptno=empdepttemp.dno
    AND m.deptno=d2.deptno;
```

* \*\*第八步：\*\*查询出领导所在的部门人数

  * 要查询出领导所在的部门编号：

  ```sql
  SELECT deptno dno,COUNT(*) count
  FROM emp
  WHERE deptno IN (
      SELECT deptno
      FROM emp
      WHERE mgr IN (
          SELECT mgr FROM emp))
  GROUP BY deptno;
  ```

  * 将两个查询整合

  ```sql
  SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,
      m.ename,m.job,mgrjobtemp.count,d.dname,empdepttemp.count,
      d2.dname,mgrdepttemp.count
  FROM emp e,(
      SELECT job,COUNT(*) count
      FROM emp
      GROUP BY job) empjobtemp,emp m,(
      SELECT job,COUNT(*) count
      FROM emp
      WHERE job IN (
          SELECT job
          FROM emp
          WHERE empno IN (
              SELECT DISTINCT mgr FROM emp))
      GROUP BY job) mgrjobtemp,dept d,(
      SELECT deptno dno,COUNT(*) count
      FROM emp
      GROUP BY deptno) empdepttemp,dept d2,(
      SELECT deptno dno,COUNT(*) count
      FROM emp
      WHERE deptno IN (
          SELECT deptno
          FROM emp
          WHERE mgr IN (
              SELECT mgr FROM emp))
      GROUP BY deptno) mgrdepttemp
  WHERE e.job=empjobtemp.job
      AND e.mgr=m.empno(+)
      AND m.job=mgrjobtemp.job
      AND e.deptno=d.deptno
      AND e.deptno=empdepttemp.dno
      AND m.deptno=d2.deptno
      AND m.deptno=mgrdepttemp.dno;
  ```
* \*\*第九步：\*\*雇员工资等级，直接引入salgrade表

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,
    m.ename,m.job,mgrjobtemp.count,d.dname,empdepttemp.count,
    d2.dname,mgrdepttemp.count,s.grade
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m,(
    SELECT job,COUNT(*) count
    FROM emp
    WHERE job IN (
        SELECT job
        FROM emp
        WHERE empno IN (
            SELECT DISTINCT mgr FROM emp))
    GROUP BY job) mgrjobtemp,dept d,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    GROUP BY deptno) empdepttemp,dept d2,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    WHERE deptno IN (
        SELECT deptno
        FROM emp
        WHERE mgr IN (
            SELECT mgr FROM emp))
    GROUP BY deptno) mgrdepttemp,salgrade s
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+)
    AND m.job=mgrjobtemp.job
    AND e.deptno=d.deptno
    AND e.deptno=empdepttemp.dno
    AND m.deptno=d2.deptno
    AND m.deptno=mgrdepttemp.dno
    AND e.sal BETWEEN s.losal AND s.hisal;
```

* \*\*第十步：\*\*找到对应等级的人数，直接追加一个子查询即可

```sql
SELECT e.empno,e.ename,e.job,e.sal,empjobtemp.count,
    m.ename,m.job,mgrjobtemp.count,d.dname,empdepttemp.count,
    d2.dname,mgrdepttemp.count,s.grade,empsaltamp.count
FROM emp e,(
    SELECT job,COUNT(*) count
    FROM emp
    GROUP BY job) empjobtemp,emp m,(
    SELECT job,COUNT(*) count
    FROM emp
    WHERE job IN (
        SELECT job
        FROM emp
        WHERE empno IN (
            SELECT DISTINCT mgr FROM emp))
    GROUP BY job) mgrjobtemp,dept d,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    GROUP BY deptno) empdepttemp,dept d2,(
    SELECT deptno dno,COUNT(*) count
    FROM emp
    WHERE deptno IN (
        SELECT deptno
        FROM emp
        WHERE mgr IN (
            SELECT mgr FROM emp))
    GROUP BY deptno) mgrdepttemp,salgrade s,(
    SELECT s1.grade grd,COUNT(e1.empno) count
    FROM emp e1,salgrade s1
    WHERE e1.sal BETWEEN s1.losal AND s1.hisal
    GROUP BY s1.grade) empsaltamp
WHERE e.job=empjobtemp.job
    AND e.mgr=m.empno(+)
    AND m.job=mgrjobtemp.job
    AND e.deptno=d.deptno
    AND e.deptno=empdepttemp.dno
    AND m.deptno=d2.deptno
    AND m.deptno=mgrdepttemp.dno
    AND e.sal BETWEEN s.losal AND s.hisal
    AND s.grade=empsaltamp.grd;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://shepherd-xie.gitbook.io/be-a-javaer/di-4-zhang-oracle-shu-ju-ku-ji-chu/section-13.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.