多表查询-7-子查询

原创于 2025-07-15 02:00:00 发布 · 738 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sql

MySQL 专栏收录该内容

33 篇文章

订阅专栏

多表查询-7-子查询

子查询

子查询

概念

SQL 语句中嵌套 SELECT 语句, 称为嵌套查询, 又为子查询

语法

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询的分类

结果分类

根据子查询结果不同, 分为:

标量子查询(子查询的结果为单个值)
列子查询(子查询的结果为一列)
行子查询(子查询的结果为一行)
表子查询(子查询的结果为多行多列)

位置分类

根据子查询位置, 分为:

WHERE 之后
FROM 之后
SELECT 之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等), 最简单的形式, 这种子查询称为标量子查询

常用的操作符: =、 <>、 >、 >=、 <、 <=

标量子查询的案例演示

查询 “技术研发中心” 的所有员工信息

在之前创建过的 employees 表中, 存在着 dept_id 部门id字段, 根据该字段可以找到对应在 departments 表中对应的部门, 那么这题就比较容易解决了, 首先获取部门的id
```
-- 获取部门 id
select dept_id
from departments
where dept_name = '技术研发中心';
```
然后通过部门id来获取去员工信息
```
-- 根据部门id获取员工信息
select *
from employees
where dept_id = 2;
```
将前面两步🈴起来就是我们的子查询的使用了
```
select *
from employees
where dept_id = (select dept_id from departments where dept_name = '技术研发中心');
```

查询在 “赵工程师” 入职之后的员工信息

这题也是可以分为两步执行, 首先查询 “赵工程师” 的入职日期

select employees.hire_date
from employees
where emp_name = '赵工程师';

查询 “赵工程师” 的入职日期

第二步是获取在该入职日期之后入职的员工信息

select *
from employees
where hire_date > '2018-09-01';

获取在该入职日期之后入职的员工信息

将前两步合并便得到子查询的语句了

select *
from employees
where hire_date > (select hire_date from employees where emp_name = '赵工程师');

查询特定员工信息2

列子查询

子查询返回的结果是一列(可以是多行), 这种子查询称为列子查询

常用的操作符: IN、 NOT IN、 ANY、 SOME、 ALL

操作符	描述
IN	在指定的集合范围之内, 多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中, 有任意一个满足即可
SOME	与 `ANY` 同等, 使用 `SOME` 的地方都可以使用 `ANY`
ALL	子查询返回列表的所有值都必须满足

列子查询的案例演示

查询 “技术研发中心” 和 “销售中心” 的所有员工信息

拆解上面需求可得

第一步: 查询 “技术研发中心” 和 “销售中心” 的部门id

select dept_id
from departments
where dept_name in ('技术研发中心', '销售中心');

查询 “技术研发中心” 和 “销售中心” 的部门id

第二步: 查询部门id为 2 和 5 的员工信息

select *
from employees
where dept_id in (2, 5);

查询部门id为 2 和 5 的员工信息

整合步骤得到列子查询结果

select *
from employees
where dept_id in (select dept_id from departments where dept_name in ('技术研发中心', '销售中心'));

查询比 “产品研发部” 所有人工资都高的员工信息

第一步: 查询 “产品研发部” 的部门id

select dept_id
from departments
where dept_name = '产品研发部';

查询 “产品研发部” 的部门id

第二步: 获取该部门所有人的工资

select salary
from employees
where dept_id = 3;

获取该部门所有人的工资

第三步: 获取比该部门所有人工资都高的员工信息

select *
from employees
where salary > all (select salary
                    from employees
                    where dept_id = 3);

获取比该部门所有人工资都高的员工信息

整合所有步骤得到下面子查询

select *
from employees
where salary > all (select salary
                    from employees
                    where dept_id = (select dept_id
                                    from departments
                                    where dept_name = '产品研发部'));

查询比 “产品研发部” 其中任意一人工资高的员工信息

大概的步骤和第二题是一样的, 那么直接就给出 sql 大家自己研究一下就可以

select *
from employees
where salary > any (select salary
                    from employees
                    where dept_id = (select dept_id
                                    from departments
                                    where dept_name = '产品研发部'));

查询比 “产品研发部” 其中任意一人工资高的员工信息

行子查询

子查询返回的结果是一行(可以是多列), 这种子查询称为行子查询

常用的操作符: =、 <>、 IN、 NOT IN

行子查询的案例演示

查询与 “王架构师” 的薪资及直属领导相同的员工信息

因为之前做的表薪资基本不相同, 但是直属领导相同的还是有不少的, 那为了方便, 就不添加数据了, 就改了一个跟王架构师直属领导相同的人的薪资和他一样, 哈哈, 学习嘛, 图个方便
- 第一步: 筛选出 “王架构师” 的薪资和直属领导的id
```
select salary, manager_id
from employees
where emp_name = '王架构师';
```
- 第二步: 根据薪资和直属领导筛选出匹配的员工信息
```
select *
from employees
where (
    salary, manager_id) = (45000.00, 2);
```
整合一下, 可以得到下面的行子查询
```
select *
from employees
where (salary, manager_id) = (select salary, manager_id from employees where emp_name = '王架构师');
```

表子查询

子查询返回的结果是多行多列, 这种子查询称为表子查询

常用的操作符: IN

表子查询的案例演示

看了看可能现在的表不太符合既有需求, 我决定再建一个表和一些模拟数据来让题目有更多的发挥空间, 需要叠个甲, 数据都是AI生成, 如有冒犯, 请多包涵

数据准备

-- =====================================================
-- 1. 建表语句
-- =====================================================
CREATE TABLE IF NOT EXISTS dept_info (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称',
    location    VARCHAR(50) COMMENT '办公地点',
    manager     VARCHAR(20) COMMENT '部门负责人'
) COMMENT='部门信息表';

CREATE TABLE IF NOT EXISTS emp_info (
    emp_id      INT PRIMARY KEY COMMENT '员工编号',
    emp_name    VARCHAR(20) NOT NULL COMMENT '员工姓名',
    gender      ENUM('男','女') NOT NULL,
    position    VARCHAR(30) NOT NULL COMMENT '工作岗位',
    hire_date   DATE        NOT NULL COMMENT '入职时间',
    salary      DECIMAL(10,2) NOT NULL COMMENT '薪资',
    dept_id     INT NOT NULL COMMENT '所属部门',
    manager_id  INT COMMENT '直属领导员工编号',
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept_info(dept_id),
    CONSTRAINT fk_emp_mgr FOREIGN KEY (manager_id) REFERENCES emp_info(emp_id)
) COMMENT='员工信息表';

-- =====================================================
-- 2. 插入部门数据
-- =====================================================
INSERT INTO dept_info(dept_id, dept_name, location, manager)
VALUES
(10,'技术研发部','北京','李雷'),
(20,'产品部','北京','韩梅梅'),
(30,'运营部','上海','王芳'),
(40,'人力资源部','上海','赵静'),
(50,'财务部','深圳','钱伟');

-- =====================================================
-- 3. 插入 120 名员工数据
--    注意：manager_id 先全部置 NULL，后面用 UPDATE 回填
-- =====================================================
INSERT INTO emp_info(emp_id, emp_name, gender, position, hire_date, salary, dept_id, manager_id)
VALUES
-- 10 技术研发部 32 人
(1001,'张伟','男','软件工程师','2021-03-15',15000,10,NULL),
(1002,'王芳','女','软件工程师','2021-05-20',15000,10,NULL),
(1003,'李娜','女','软件工程师','2022-01-10',15000,10,NULL),
(1004,'刘洋','男','软件工程师','2021-06-18',15000,10,NULL),
(1005,'陈晨','女','测试工程师','2021-07-22',12000,10,NULL),
(1006,'赵敏','女','测试工程师','2022-02-14',12000,10,NULL),
(1007,'李雷','男','技术总监','2020-08-01',25000,10,NULL),
(1008,'周杰','男','软件工程师','2021-09-30',15000,10,NULL),
(1009,'吴磊','男','软件工程师','2022-03-11',15000,10,NULL),
(1010,'郑爽','女','前端工程师','2021-10-05',14000,10,NULL),
(1011,'杨幂','女','前端工程师','2022-04-12',14000,10,NULL),
(1012,'刘德华','男','软件工程师','2021-11-20',15000,10,NULL),
(1013,'张学友','男','运维工程师','2021-12-03',13000,10,NULL),
(1014,'郭富城','男','运维工程师','2022-05-09',13000,10,NULL),
(1015,'梁朝伟','男','算法工程师','2021-08-17',18000,10,NULL),
(1016,'林志玲','女','算法工程师','2022-01-25',18000,10,NULL),
(1017,'周杰伦','男','软件工程师','2021-07-08',15000,10,NULL),
(1018,'林俊杰','男','测试工程师','2022-06-14',12000,10,NULL),
(1019,'蔡依林','女','前端工程师','2021-12-30',14000,10,NULL),
(1020,'萧敬腾','男','软件工程师','2022-02-28',15000,10,NULL),
(1021,'彭于晏','男','软件工程师','2021-09-05',15000,10,NULL),
(1022,'章子怡','女','产品经理','2021-04-18',16000,10,NULL),
(1023,'范冰冰','女','UI设计师','2021-03-29',13500,10,NULL),
(1024,'李冰冰','女','UI设计师','2022-05-22',13500,10,NULL),
(1025,'成龙','男','技术总监','2020-10-01',23500,10,NULL),
(1026,'甄子丹','男','架构师','2021-02-08',22000,10,NULL),
(1027,'吴京','男','软件工程师','2021-06-03',15000,10,NULL),
(1028,'黄渤','男','软件工程师','2022-04-01',15000,10,NULL),
(1029,'沈腾','男','测试工程师','2021-05-16',12000,10,NULL),
(1030,'马丽','女','前端工程师','2021-11-11',14000,10,NULL),
(1031,'徐峥','男','算法工程师','2022-03-18',18000,10,NULL),
(1032,'王宝强','男','运维工程师','2021-12-25',13000,10,NULL),

-- 20 产品部 24 人
(2001,'韩梅梅','女','产品总监','2020-09-01',24000,20,NULL),
(2002,'雷军','男','产品经理','2021-04-12',16500,20,NULL),
(2003,'董明珠','女','产品经理','2021-05-30',16500,20,NULL),
(2004,'罗永浩','男','产品经理','2022-02-16',16500,20,NULL),
(2005,'贾跃亭','男','交互设计师','2021-07-07',14000,20,NULL),
(2006,'王思聪','男','交互设计师','2022-01-19',14000,20,NULL),
(2007,'刘强东','男','产品经理','2021-08-08',16500,20,NULL),
(2008,'马云','男','产品总监','2020-07-15',23000,20,NULL),
(2009,'马化腾','男','产品经理','2021-09-09',16500,20,NULL),
(2010,'李彦宏','男','数据分析师','2021-10-10',14500,20,NULL),
(2011,'张一鸣','男','数据分析师','2022-02-20',14500,20,NULL),
(2012,'丁磊','男','UI设计师','2021-11-11',13500,20,NULL),
(2013,'周鸿祎','男','产品经理','2021-12-12',16500,20,NULL),
(2014,'史玉柱','男','交互设计师','2022-03-03',14000,20,NULL),
(2015,'柳传志','男','产品总监','2020-06-01',24500,20,NULL),
(2016,'杨元庆','男','产品经理','2021-02-14',16500,20,NULL),
(2017,'郭台铭','男','数据分析师','2021-03-08',14500,20,NULL),
(2018,'任正非','男','产品经理','2021-04-25',16500,20,NULL),
(2019,'余承东','男','交互设计师','2021-05-21',14000,20,NULL),
(2020,'张小龙','男','产品经理','2021-06-30',16500,20,NULL),
(2021,'程维','男','数据分析师','2022-01-05',14500,20,NULL),
(2022,'宿华','男','UI设计师','2021-07-18',13500,20,NULL),
(2023,'王小川','男','产品经理','2021-08-28',16500,20,NULL),
(2024,'姚劲波','男','产品总监','2020-05-05',23500,20,NULL),

-- 30 运营部 24 人
(3001,'王芳','女','运营总监','2020-08-18',23000,30,NULL),
(3002,'赵丽颖','女','内容运营','2021-03-27',12500,30,NULL),
(3003,'冯绍峰','男','内容运营','2021-04-16',12500,30,NULL),
(3004,'朱一龙','男','用户运营','2021-05-12',13000,30,NULL),
(3005,'杨紫','女','用户运营','2022-02-08',13000,30,NULL),
(3006,'张一山','男','电商运营','2021-06-09',13500,30,NULL),
(3007,'李现','男','电商运营','2021-07-17',13500,30,NULL),
(3008,'迪丽热巴','女','新媒体运营','2021-08-24',12800,30,NULL),
(3009,'古力娜扎','女','新媒体运营','2022-03-15',12800,30,NULL),
(3010,'杨超越','女','内容运营','2021-09-28',12500,30,NULL),
(3011,'王一博','男','用户运营','2021-10-30',13000,30,NULL),
(3012,'肖战','男','电商运营','2021-11-11',13500,30,NULL),
(3013,'邓伦','男','新媒体运营','2021-12-12',12800,30,NULL),
(3014,'黄景瑜','男','运营总监','2020-07-01',22500,30,NULL),
(3015,'许魏洲','男','内容运营','2022-01-20',12500,30,NULL),
(3016,'白敬亭','男','用户运营','2021-02-14',13000,30,NULL),
(3017,'井柏然','男','电商运营','2021-03-08',13500,30,NULL),
(3018,'杨洋','男','新媒体运营','2021-04-10',12800,30,NULL),
(3019,'李易峰','男','内容运营','2021-05-25',12500,30,NULL),
(3020,'陈伟霆','男','用户运营','2021-06-19',13000,30,NULL),
(3021,'吴彦祖','男','电商运营','2022-05-05',13500,30,NULL),
(3022,'金城武','男','新媒体运营','2021-07-07',12800,30,NULL),
(3023,'刘德华','男','运营总监','2020-09-09',22000,30,NULL),
(3024,'张学友','男','内容运营','2021-08-08',12500,30,NULL),

-- 40 人力资源部 20 人
(4001,'赵静','女','HRD','2020-05-01',20000,40,NULL),
(4002,'孙红雷','男','招聘经理','2021-03-03',14000,40,NULL),
(4003,'黄渤','男','招聘经理','2021-04-04',14000,40,NULL),
(4004,'徐峥','男','培训经理','2021-05-05',15000,40,NULL),
(4005,'沈腾','男','培训经理','2022-02-02',15000,40,NULL),
(4006,'马丽','女','薪酬专员','2021-06-06',11000,40,NULL),
(4007,'杜华','女','薪酬专员','2021-07-07',11000,40,NULL),
(4008,'宋茜','女','HRBP','2021-08-08',13500,40,NULL),
(4009,'吴奇隆','男','HRBP','2021-09-09',13500,40,NULL),
(4010,'刘诗诗','女','招聘经理','2021-10-10',14000,40,NULL),
(4011,'霍建华','男','培训经理','2021-11-11',15000,40,NULL),
(4012,'林心如','女','薪酬专员','2021-12-12',11000,40,NULL),
(4013,'范冰冰','女','HRBP','2022-01-13',13500,40,NULL),
(4014,'李冰冰','女','招聘经理','2021-02-14',14000,40,NULL),
(4015,'成龙','男','培训经理','2021-03-15',15000,40,NULL),
(4016,'吴京','男','薪酬专员','2021-04-16',11000,40,NULL),
(4017,'黄渤','男','HRBP','2021-05-17',13500,40,NULL),
(4018,'沈腾','男','招聘经理','2021-06-18',14000,40,NULL),
(4019,'马丽','女','培训经理','2021-07-19',15000,40,NULL),
(4020,'徐峥','男','HRD','2020-06-01',19500,40,NULL),

-- 50 财务部 20 人
(5001,'钱伟','男','CFO','2020-04-01',22000,50,NULL),
(5002,'赵薇','女','财务经理','2021-03-05',15500,50,NULL),
(5003,'黄晓明','男','财务经理','2021-04-06',15500,50,NULL),
(5004,'章子怡','女','会计','2021-05-07',11500,50,NULL),
(5005,'巩俐','女','会计','2021-06-08',11500,50,NULL),
(5006,'周润发','男','出纳','2021-07-09',10500,50,NULL),
(5007,'周星驰','男','出纳','2022-01-10',10500,50,NULL),
(5008,'梁朝伟','男','审计经理','2021-08-11',16000,50,NULL),
(5009,'张曼玉','女','审计经理','2021-09-12',16000,50,NULL),
(5010,'刘嘉玲','女','财务经理','2021-10-13',15500,50,NULL),
(5011,'古天乐','男','会计','2021-11-14',11500,50,NULL),
(5012,'郭富城','男','出纳','2021-12-15',10500,50,NULL),
(5013,'黎明','男','审计经理','2022-02-16',16000,50,NULL),
(5014,'刘德华','男','CFO','2020-05-01',21500,50,NULL),
(5015,'张学友','男','财务经理','2021-03-17',15500,50,NULL),
(5016,'成龙','男','会计','2021-04-18',11500,50,NULL),
(5017,'吴京','男','出纳','2021-05-19',10500,50,NULL),
(5018,'黄渤','男','审计经理','2021-06-20',16000,50,NULL),
(5019,'沈腾','男','财务经理','2021-07-21',15500,50,NULL),
(5020,'马丽','女','会计','2021-08-22',11500,50,NULL);

-- =====================================================
-- 4. 回填直属领导（manager_id）
--    规则示例：
--    技术研发部：李雷(1007) 带 1001-1006,1008-1012
--               成龙(1025) 带 1013-1024
--    产品部：韩梅梅(2001) 带 2002-2013
--             柳传志(2015) 带 2014-2024
--    运营部：王芳(3001) 带 3002-3012
--             黄景瑜(3014) 带 3015-3024
--    人力资源部：赵静(4001) 带 4002-4011
--                 徐峥(4020) 带 4012-4020
--    财务部：钱伟(5001) 带 5002-5011
--             刘德华(5014) 带 5012-5020
-- =====================================================
UPDATE emp_info SET manager_id = 1007 WHERE emp_id BETWEEN 1001 AND 1006;
UPDATE emp_info SET manager_id = 1007 WHERE emp_id BETWEEN 1008 AND 1012;
UPDATE emp_info SET manager_id = 1025 WHERE emp_id BETWEEN 1013 AND 1024;

UPDATE emp_info SET manager_id = 2001 WHERE emp_id BETWEEN 2002 AND 2013;
UPDATE emp_info SET manager_id = 2015 WHERE emp_id BETWEEN 2014 AND 2024;

UPDATE emp_info SET manager_id = 3001 WHERE emp_id BETWEEN 3002 AND 3012;
UPDATE emp_info SET manager_id = 3014 WHERE emp_id BETWEEN 3015 AND 3024;

UPDATE emp_info SET manager_id = 4001 WHERE emp_id BETWEEN 4002 AND 4011;
UPDATE emp_info SET manager_id = 4020 WHERE emp_id BETWEEN 4012 AND 4020;

UPDATE emp_info SET manager_id = 5001 WHERE emp_id BETWEEN 5002 AND 5011;
UPDATE emp_info SET manager_id = 5014 WHERE emp_id BETWEEN 5012 AND 5020;

查询与 “周杰伦”, “林俊杰” 的职位和薪资相同的员工信息

第一步: 查询 “周杰伦”, “林俊杰” 的职位和薪资

select emp_info.position, emp_info.salary
from emp_info
where emp_name in ('周杰伦', '林俊杰');

查询 “周杰伦”, “林俊杰” 的职位和薪资

第二步: 根据职位和薪资查询员工信息

select *
from emp_info
where (position, salary) in (('软件工程师', 15000.00), ('测试工程师', 12000.00));

根据职位和薪资查询员工信息

整合上面信息可得到表子查询语句

select *
from emp_info
where (position, salary) in (select emp_info.position, emp_info.salary
                            from emp_info
                            where emp_name in ('周杰伦', '林俊杰'));

查询入职日期是 “2022-02-16” 之后的员工信息, 及其部门信息

第一步: 查询入职日期"2022-02-16" 之后的员工信息

select *
from emp_info
where hire_date > '2022-02-16';

查询入职日期"2022-02-16" 之后的员工信息

第二步: 查询该部分员工对应的部门信息

select *
from dept_info
where dept_id in (10, 20, 30, 40, 50);

查询该部分员工对应的部门信息

整合上面部分, 便可得到对应的表查询语句

select *
from (select * from emp_info where hire_date > '2022-02-16') as e
        left join dept_info as d on e.dept_id = d.dept_id;

其实也可以使用左外查询的办法, 查询出来结果是一样的

select *
from emp_info as e
        left join dept_info as d on e.dept_id = d.dept_id
where hire_date > '2022-02-16';