2020-10-26 12:59 已编辑门头沟学院 Java

关注

<span>DQL -- 子查询（Subquery）</span>

子查询

含义：出现在其他语句中的select语句，称为子查询或者内查询
外部的查询语句，叫做主查询或外查询
分类
- 按子查询出现的位置：
  - select语句
    只支持标量子查询
    - from语句
      支持表子查询
    - where或者having后面（重点）
      标量子查询（单行）
      列子查询（多行）
      行子查询（使用较少）
    - exists语句后面（相关子查询）
      表子查询
- 按结果集的行列数不同
  + 标量子查询（结果只有一行一列）
  + 列子查询（只有一列多行）
  + 行子查询（结果可以有一行一列）
  + 表子查询（结果集一般为多行多列）

一、where或者having后面的子查询

列子查询多行比较运算符
1. IN/NOT IN：等于列表中的任意一个
2. ANY/SOME：和子查询列表中的某一个比较（类似于每一个值比较都是或运算）
3. ALL：和子查询列表中的每一个值比较（类似于每一个值比较都是与运算）
特点
1. 子查询放在小括号里面
2. 子查询一般放在条件的最右侧
3. 标量子查询，一般搭配单行操作符
<, > , <=, >=, =, !=
1. 列子查询，一般搭配多行操作符使用
in, any/some, all
1. 子查询的执行优先与主查询执行

标量子查询（单行子查询）

案例1：谁的工资比Abel高

SELECT 
    last_name, salary
FROM
    employees
WHERE
    salary > (
		SELECT 
            salary
        FROM
            employees
        WHERE
            last_name = 'Abel');

案例2：返回job_id与141号员工相同，salary比141号员工多的员工姓名，job_id和工资

执行单子句查询

SELECT 
    last_name, job_id, salary
FROM
    employees
WHERE
    job_id = (SELECT 
            job_id
        FROM
            employees
        WHERE
            employee_id = 141)
        AND salary > (SELECT 
            salary
        FROM
            employees
        WHERE
            employee_id = 143);

案例3：返回公司工资最少的员工的last_name，job_id和salary

子查询中使用组函数

SELECT 
    last_name, job_id, salary
FROM
    employees
WHERE
    salary = (SELECT 
            MIN(salary)
        FROM
            employees);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

子句中的HAVING语句

SELECT 
    department_id, MIN(salary)
FROM
    employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT 
        MIN(salary)
    FROM
        employees
    WHERE
        department_id = 50);

列子查询（多行子查询）

案例1：返回location_id是1400或1700的部门中的所有员工姓名

# 连接查询
SELECT 
    d.location_id, e.last_name
FROM
    departments d
        INNER JOIN
    employees e ON e.department_id = d.department_id
WHERE
    d.location_id IN (1400 , 1700);
# 列子查询
SELECT 
    last_name
FROM
    employees
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400 , 1700));

案例2：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的工号，姓名，job_id，以及salary

在多行子查询中使用ANY操作符

SELECT 
    employee_id, last_name, job_id, salary
FROM
    employees
WHERE
    salary < ANY (SELECT DISTINCT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG')
        AND job_id <> 'IT_PROG';

案例3：回其他部门中比job_id为'IT_PROG'部门所有工资低的员工的工号，姓名，job_id，以及salary

在多行子查询中使用ALL操作符

SELECT 
    employee_id, last_name, job_id, salary
FROM
    employees
WHERE
    salary < ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG')
        AND job_id <> 'IT_PROG';

行子查询（一行多列，多行多列）

案例：查询员工编号最小的并且是工资最高的员工工号。姓名，工资，job_id

SELECT 
    employee_id, last_name, salary, job_id
FROM
    employees
WHERE
    employee_id = (SELECT 
            MIN(employee_id)
        FROM
            employees)
        AND salary = (SELECT 
            MAX(salary)
        FROM
            employees);

二、select语句后面的子查询

select内部仅仅支持标量子查询

案例1：查询每个部门的员工个数

# 子查询
SELECT 
    d.department_id,
    (SELECT 
            COUNT(*)
        FROM
            employees e
        WHERE
            e.department_id = d.department_id) COUNT
FROM
    departments d;
# 分组查询
SELECT 
    department_id, COUNT(last_name) COUNT
FROM
    employees
GROUP BY department_id;

案例2：查询员工号等于102的部门名

# 子查询版本
SELECT 
    department_name
FROM
    departments
WHERE
    department_id = (SELECT 
            department_id
        FROM
            employees
        WHERE
            employee_id = 102);
# 连接查询
SELECT 
    d.department_name
FROM
    departments d
        INNER JOIN
    employees e ON e.department_id = d.department_id
WHERE
    e.employee_id = 102;

三、from后面的子查询

案例1：查询每个部门的平均工资的工资等级

SELECT 
    A.department_id, round(A.AVG_SAL, 2), j.grade_level
FROM
    (SELECT 
        AVG(salary) AVG_SAL, department_id
    FROM
        employees
    GROUP BY department_id) A
        INNER JOIN
    job_grades j ON A.AVG_SAL BETWEEN lowest_sal AND highest_sal;

四、exists后面的子查询（相关子查询）

# 判断子查询里面有没有值
SELECT 
    EXISTS( SELECT 
            employee_id
        FROM
            employees); # 1 有
SELECT 
    EXISTS( SELECT 
            salary
        FROM
            employees
        WHERE
            salary = 30000); # 0 没有

案例：查询员工的部门名

# exists版本
SELECT 
    department_name
FROM
    departments d
WHERE
    EXISTS( SELECT 
            *
        FROM
            employees e
        WHERE
            d.department_id = e.department_id);
# in版本
SELECT 
    department_name
FROM
    departments d
WHERE
    d.department_id IN (SELECT 
            department_id
        FROM
            employees e
        WHERE
            e.department_id = d.department_id);

相关测试

查询和Zlotkey相同部门的员工姓名和工资

SELECT 
    last_name, salary
FROM
    employees
WHERE
    department_id = (SELECT 
            department_id
        FROM
            employees
        WHERE
            last_name = 'Zlotkey');

查询工资比公司平均工资高得员工的员工号，姓名和工资

SELECT 
    employee_id, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);

查询各部门中工资比本部门平均工资高的员工的员工号和姓名

SELECT 
    employee_id, last_name
FROM
    employees e
        INNER JOIN
    (SELECT 
        AVG(salary) av, department_id
    FROM
        employees
    GROUP BY department_id) A ON e.department_id = A.department_id
WHERE
    e.salary > A.av;

查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT 
    employee_id, last_name
FROM
    employees e
WHERE
    e.department_id IN (SELECT DISTINCT
            department_id
        FROM
            employees
        WHERE
            last_name LIKE '%u%');

查询在部门的location_id为1700的部门工作的员工的员工号

SELECT 
    employee_id
FROM
    employees
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id = 1700);

查询管理者是K_ing的员工姓名和工资

SELECT 
    last_name, salary
FROM
    employees
WHERE
    manager_id IN (SELECT 
            employee_id
        FROM
            employees
        WHERE
            last_name = 'K_ing');

查询工资最高的员工姓名，要求first_name和last_name为一列

SELECT 
    CONCAT(first_name, ' ', last_name)
FROM
    employees
WHERE
    salary = (SELECT 
            MAX(salary)
        FROM
            employees);

全部评论

推荐最新楼层

11-20 04:45

门头沟学院 C++

腾讯面经

迟来的面经。。。因为直到今日我居然还没有面完。鹅厂的面试跨度有点大，以至于我甚至忘掉了相当多的部分，于是只能简单描述一下。一面：项目拷打，几乎没有八股，最后是一道算法题，写出来了，但是没写出最优的来。二面：项目拷打，无八股，无算法题。三面：项目拷打，然后是发散性题目，比如：“你对于XX领域（面试的岗位相关）怎么看的啊？”，“你以前是做A的，为什么想做B？这个gap你是怎么看的？”四面：自我介绍，然后是，“你刚刚提到你的一个观点XXX，你能具体描述一下吗？” 于是拿了自己经历过的两个项目来佐证；“现在有最新的研究尝试把机器学习替换掉某些关键的组件，比如XX，你了解过吗？” 正好之前了解过相关的内...

查看5道真题和解析

点赞评论收藏

11-21 11:25

门头沟学院算法工程师

字节怎么才能看到面评

如题，字节跳动怎么才能看到自己的面评，找hr说看不到

SoulStar：自己应该看不到，这个是字节比较保密的信息，之前有mentor加我，说他能看到，但是不能给我说，给我说了他可能就要被辞退了

点赞评论收藏

11-02 12:31

燕山大学机械工程师

菜鸡offer选择！

投票

下周做决定，签三方了，麻烦各位了，评论区畅所欲言！ #机械人的offer怎么选# #offer帮选# #华为求职进展汇总# 华为引流

Offer来来来2000：福利：免费饮水。是我理解错了吗

机械人的offer怎么选 offer帮选

点赞评论收藏

11-11 15:12

南昌大学材料工程师

完辣

211硕双非本，原来这么垃圾？投一次被拒一次，笑死。 #0offer是寒冬太冷还是我太菜#

一个菜鸡罢了：哥们，感觉你的简历还是有点问题的，我提几点建议，看看能不能提供一点帮助 1. ”新余学院“别加粗，课程不清楚是否有必要写，感觉版面不如拿来写一下做过的事情，教育经历是你的弱势就尽量少写 2. “干部及社团经历”和“自我评价”删掉 3. 论文后面的“录用”和“小修”啥的都删掉，默认全录用，问了再说，反正小修毕业前肯定能发出来 4. 工作经验和研究成果没有体现你的个人贡献，着重包装一下个人贡献

0offer是寒冬太冷还是我太菜

点赞评论收藏