MySQL复合查询从基础到高级应用全面解析
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、复合查询基础概念</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 什么是复合查询</a></li><li><a href="#_lab2_0_1">1.2 复合查询的主要类型</a></li></ul><li><a href="#_label1">二、示例数据库结构详解</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_2">2.1 完整的表结构设计</a></li><li><a href="#_lab2_1_3">2.2 示例数据填充</a></li></ul><li><a href="#_label2">三、子查询深度解析</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_4">3.1 子查询分类与语法</a></li><ul class="third_class_ul"><li><a href="#_label3_2_4_0">3.1.1 按子查询位置分类</a></li><li><a href="#_label3_2_4_1">3.1.2 按子查询相关性分类</a></li></ul><li><a href="#_lab2_2_5">3.2 子查询操作符详解</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_6">3.3 子查询性能优化</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label3">四、连接查询全面讲解</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_7">4.1 连接类型详解</a></li><ul class="third_class_ul"><li><a href="#_label3_3_7_2">4.1.1 内连接(INNER JOIN)</a></li><li><a href="#_label3_3_7_3">4.1.2 外连接(OUTER JOIN)</a></li><li><a href="#_label3_3_7_4">4.1.3 交叉连接(CROSS JOIN)</a></li><li><a href="#_label3_3_7_5">4.1.4 自连接(SELF JOIN)</a></li></ul><li><a href="#_lab2_3_8">4.2 连接查询优化策略</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">五、UNION查询高级应用</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_9">5.1 UNION基础用法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_10">5.2 UNION ALL与UNION的区别</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_11">5.3 复杂UNION查询示例</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">六、派生表与CTE高级用法</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_12">6.1 派生表(MySQL 5.7+)</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_13">6.2 公用表表达式(CTE, MySQL 8.0+)</a></li><ul class="third_class_ul"><li><a href="#_label3_5_13_6">6.2.1 基本CTE</a></li><li><a href="#_label3_5_13_7">6.2.2 递归CTE</a></li></ul></ul><li><a href="#_label6">七、复合查询实战案例</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_14">7.1 多层级数据分析</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_6_15">7.2 复杂业务逻辑实现</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label7">八、性能优化与最佳实践</a></li><ul class="second_class_ul"><li><a href="#_lab2_7_16">8.1 复合查询性能优化</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_7_17">8.2 复合查询最佳实践</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label8">九、常见问题与解决方案</a></li><ul class="second_class_ul"><li><a href="#_lab2_8_18">9.1 性能问题排查</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_8_19">9.2 结果不符合预期</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_8_20">9.3 语法错误处理</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label9">十、总结与进阶学习建议</a></li><ul class="second_class_ul"><li><a href="#_lab2_9_21">10.1 复合查询核心要点总结</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_9_22">10.2 进阶学习建议</a></li><ul class="third_class_ul"></ul></ul></ul></div><p><strong>前言：</strong></p><blockquote><p>前面学习了表的增删查改之后，今天我们重点来讲解一下有关查询的复杂问题——复合查询</p></blockquote>
<p class="maodian"><a name="_label0"></a></p><h2>一、复合查询基础概念</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 什么是复合查询</h3>
<p>复合查询是指将多个简单查询通过特定的SQL语法组合起来，形成一个功能更加强大的查询语句。与简单查询相比，复合查询能够：</p>
<ul><li>处理更复杂的数据关系</li><li>减少应用程序中的数据处理逻辑</li><li>提高数据检索效率（当正确使用时）</li><li>实现跨表的数据关联和分析</li></ul>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>1.2 复合查询的主要类型</h3>
<p>MySQL中常见的复合查询包括：</p>
<ul><li>子查询（Subqueries）</li><li>连接查询（JOIN Operations）</li><li>联合查询（UNION Queries）</li><li>派生表（Derived Tables）</li><li>公用表表达式（Common Table Expressions，CTE）</li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>二、示例数据库结构详解</h2>
<p>在进行讲解我们的查询之前，我们先看一下名为需要用到的表，以及往表里添加几组示例数据，以方便我们查询后看到查询的效果</p>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>2.1 完整的表结构设计</h3>
<div class="jb51code"><pre class="brush:sql;">-- 部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL,
established_date DATE,
budget DECIMAL(12,2)
);
-- 员工表
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
dept_id INT,
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL,
manager_id INT,
email VARCHAR(100),
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);
-- 项目表
CREATE TABLE projects (
project_id INT PRIMARY KEY AUTO_INCREMENT,
project_name VARCHAR(100) NOT NULL,
budget DECIMAL(12,2),
start_date DATE,
end_date DATE,
dept_id INT,
status ENUM('Planning', 'In Progress', 'Completed', 'On Hold') DEFAULT 'Planning',
CONSTRAINT fk_project_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 员工项目关联表
CREATE TABLE emp_projects (
emp_id INT,
project_id INT,
role VARCHAR(50),
join_date DATE,
hours_allocated INT,
PRIMARY KEY (emp_id, project_id),
CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects(project_id)
);</pre></div>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.2 示例数据填充</h3>
<div class="jb51code"><pre class="brush:sql;">-- 部门数据
INSERT INTO departments VALUES
(1, '技术研发部', '北京总部', '2015-06-01', 2000000.00),
(2, '市场营销部', '上海分公司', '2016-03-15', 1500000.00),
(3, '人力资源部', '广州办事处', '2017-01-10', 800000.00),
(4, '财务部', '北京总部', '2015-06-01', 1200000.00);
-- 员工数据
INSERT INTO employees VALUES
(1, '张伟', 1, 25000.00, '2016-03-10', NULL, 'zhangwei@company.com'),
(2, '李娜', 1, 18000.00, '2017-05-15', 1, 'lina@company.com'),
(3, '王芳', 2, 22000.00, '2016-11-20', NULL, 'wangfang@company.com'),
(4, '赵刚', 2, 16000.00, '2018-02-28', 3, 'zhaogang@company.com'),
(5, '钱强', 3, 19000.00, '2017-08-05', NULL, 'qianqiang@company.com'),
(6, '孙丽', 3, 14000.00, '2019-06-15', 5, 'sunli@company.com'),
(7, '周明', 4, 21000.00, '2016-07-22', NULL, 'zhouming@company.com');
-- 项目数据
INSERT INTO projects VALUES
(1, '新一代电商平台开发', 800000.00, '2023-01-10', '2023-09-30', 1, 'In Progress'),
(2, '全球市场推广计划', 500000.00, '2023-02-15', '2023-08-15', 2, 'In Progress'),
(3, '员工技能提升计划', 200000.00, '2023-03-01', '2023-12-31', 3, 'Planning'),
(4, '财务系统云迁移', 350000.00, '2023-04-01', NULL, 4, 'In Progress'),
(5, '移动端应用优化', 300000.00, '2023-05-15', '2023-11-30', 1, 'Planning');
-- 员工项目关联
INSERT INTO emp_projects VALUES
(1, 1, '技术负责人', '2023-01-05', 30),
(2, 1, '开发工程师', '2023-01-10', 40),
(1, 5, '架构师', '2023-05-10', 20),
(3, 2, '市场总监', '2023-02-10', 25),
(4, 2, '市场专员', '2023-02-15', 35),
(5, 3, '培训经理', '2023-03-01', 30),
(6, 3, '培训助理', '2023-03-05', 20),
(7, 4, '项目经理', '2023-04-01', 40);</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、子查询深度解析</h2>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>3.1 子查询分类与语法</h3>
<p class="maodian"><a name="_label3_2_4_0"></a></p><h4>3.1.1 按子查询位置分类</h4>
<p><strong>WHERE子句子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511414.png" /></p>
<ol><li><p><strong>FROM子句子查询（派生表）</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT d.dept_name, avg_sal.avg_salary
FROM departments d
JOIN (SELECT dept_id, AVG(salary) as avg_salary
FROM employees GROUP BY dept_id) avg_sal
ON d.dept_id = avg_sal.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511425.png" /></p></li><li><p><strong>SELECT子句子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name, salary,
(SELECT AVG(salary) FROM employees) as company_avg
FROM employees;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511440.png" /></p></li><li><p><strong>HAVING子句子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511461.png" /></p></li></ol>
<p class="maodian"><a name="_label3_2_4_1"></a></p><h4>3.1.2 按子查询相关性分类</h4>
<p><strong>非相关子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京总部');</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511418.png" /></p>
<p><strong>相关子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT e1.emp_name, e1.salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511471.png" /></p>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>3.2 子查询操作符详解</h3>
<p><strong>IN操作符</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 1000000);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511445.png" /></p>
<p><strong>NOT IN操作符</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name
FROM employees
WHERE emp_id NOT IN (SELECT DISTINCT emp_id FROM emp_projects);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511429.png" /></p>
<p><strong>EXISTS操作符</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM projects p
WHERE p.dept_id = d.dept_id AND p.status = 'In Progress');</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511516.png" /></p>
<p><strong>比较运算符子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT emp_name, salary
FROM employees
WHERE salary >= (SELECT MAX(salary) * 0.8 FROM employees);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511596.png" /></p>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>3.3 子查询性能优化</h3>
<p><strong>使用JOIN替代子查询</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 不推荐
SELECT emp_name FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京总部');
-- 推荐
SELECT e.emp_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = '北京总部';</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511612.png" /></p>
<p><strong>使用EXISTS替代IN</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 当子查询结果集大时更高效
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM projects p
WHERE p.dept_id = d.dept_id);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511512.png" /></p>
<p><strong>限制子查询返回的列数</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 只选择必要的列
SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments);-- 而不是 SELECT *</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511637.png" /></p>
<p class="maodian"><a name="_label3"></a></p><h2>四、连接查询全面讲解</h2>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>4.1 连接类型详解</h3>
<p class="maodian"><a name="_label3_3_7_2"></a></p><h4>4.1.1 内连接(INNER JOIN)</h4>
<div class="jb51code"><pre class="brush:sql;">-- 基本内连接
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 带条件的内连接
SELECT e.emp_name, p.project_name, ep.role
FROM employees e
INNER JOIN emp_projects ep ON e.emp_id = ep.emp_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE p.status = 'In Progress';</pre></div>
<p class="maodian"><a name="_label3_3_7_3"></a></p><h4>4.1.2 外连接(OUTER JOIN)</h4>
<p><strong>左外连接(LEFT JOIN)</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 查询所有部门及其员工(包括没有员工的部门)
SELECT d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511682.png" /></p>
<p><strong>右外连接(RIGHT JOIN)</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 查询所有员工及其部门(包括没有部门的员工)
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511668.png" /></p>
<p><strong>全外连接(FULL OUTER JOIN) - MySQL通过UNION实现</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 查询所有员工和所有部门的组合
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511647.png" /></p>
<p class="maodian"><a name="_label3_3_7_4"></a></p><h4>4.1.3 交叉连接(CROSS JOIN)</h4>
<div class="jb51code"><pre class="brush:sql;">-- 生成员工和项目的所有可能组合
SELECT e.emp_name, p.project_name
FROM employees e
CROSS JOIN projects p;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511651.jpg" /></p>
<p class="maodian"><a name="_label3_3_7_5"></a></p><h4>4.1.4 自连接(SELF JOIN)</h4>
<div class="jb51code"><pre class="brush:sql;">-- 查询员工及其经理信息
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511714.png" /></p>
<p class="maodian"><a name="_lab2_3_8"></a></p><h3>4.2 连接查询优化策略</h3>
<p><strong><span>下面关于索引和视图的知识后面还会详细讲解</span></strong></p>
<p><strong>确保连接条件有索引</strong></p>
<div class="jb51code"><pre class="brush:sql;">ALTER TABLE employees ADD INDEX idx_dept_id (dept_id);
ALTER TABLE emp_projects ADD INDEX idx_emp_id (emp_id);
ALTER TABLE emp_projects ADD INDEX idx_project_id (project_id);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511788.png" /></p>
<p><strong>选择适当的连接顺序</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 小表驱动大表原则
SELECT /*+ JOIN_ORDER(d, e) */ d.dept_name, e.emp_name
FROM departments d-- 假设部门表比员工表小
JOIN employees e ON d.dept_id = e.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511793.png" /></p>
<p><strong>使用STRAIGHT_JOIN强制连接顺序</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT STRAIGHT_JOIN d.dept_name, COUNT(e.emp_id) as emp_count
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511734.png" /></p>
<p class="maodian"><a name="_label4"></a></p><h2>五、UNION查询高级应用</h2>
<p class="maodian"><a name="_lab2_4_9"></a></p><h3>5.1 UNION基础用法</h3>
<div class="jb51code"><pre class="brush:sql;">-- 合并员工和部门名称
SELECT emp_name AS name, 'Employee' AS type FROM employees
UNION
SELECT dept_name, 'Department' FROM departments
ORDER BY type, name;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511830.png" /></p>
<p class="maodian"><a name="_lab2_4_10"></a></p><h3>5.2 UNION ALL与UNION的区别</h3>
<div class="jb51code"><pre class="brush:sql;">-- UNION会去重，UNION ALL不会
SELECT dept_id FROM employees WHERE salary > 20000
UNION
SELECT dept_id FROM departments WHERE budget > 1500000;
-- 使用UNION ALL提高性能(当确定不需要去重时)
SELECT emp_name FROM employees WHERE dept_id = 1
UNION ALL
SELECT emp_name FROM employees WHERE salary > 18000;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511813.png" /></p>
<p class="maodian"><a name="_lab2_4_11"></a></p><h3>5.3 复杂UNION查询示例</h3>
<div class="jb51code"><pre class="brush:sql;">-- 按类型统计人数和预算
SELECT 'Department' AS category, COUNT(*) AS count, SUM(budget) AS total_budget
FROM departments
UNION
SELECT 'Employee' AS category, COUNT(*) AS count, SUM(salary) AS total_salary
FROM employees
UNION
SELECT 'Project' AS category, COUNT(*) AS count, SUM(budget) AS total_budget
FROM projects;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511880.png" /></p>
<p class="maodian"><a name="_label5"></a></p><h2>六、派生表与CTE高级用法</h2>
<p class="maodian"><a name="_lab2_5_12"></a></p><h3>6.1 派生表(MySQL 5.7+)</h3>
<div class="jb51code"><pre class="brush:sql;">-- 计算各部门薪资统计信息
SELECT d.dept_name,
stats.emp_count,
stats.avg_salary,
stats.max_salary
FROM departments d
JOIN (
SELECT dept_id,
COUNT(*) as emp_count,
AVG(salary) as avg_salary,
MAX(salary) as max_salary
FROM employees
GROUP BY dept_id
) stats ON d.dept_id = stats.dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511837.png" /></p>
<p class="maodian"><a name="_lab2_5_13"></a></p><h3>6.2 公用表表达式(CTE, MySQL 8.0+)</h3>
<p class="maodian"><a name="_label3_5_13_6"></a></p><h4>6.2.1 基本CTE</h4>
<div class="jb51code"><pre class="brush:sql;">-- 查询参与项目的员工信息
WITH project_emps AS (
SELECT DISTINCT emp_id FROM emp_projects
)
SELECT e.emp_name, e.salary
FROM employees e
JOIN project_emps pe ON e.emp_id = pe.emp_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511996.png" /></p>
<p class="maodian"><a name="_label3_5_13_7"></a></p><h4>6.2.2 递归CTE</h4>
<div class="jb51code"><pre class="brush:sql;">-- 组织结构层级查询
WITH RECURSIVE org_hierarchy AS (
-- 基础查询：找出所有没有经理的员工(顶层管理者)
SELECT emp_id, emp_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归查询：找出每个员工的下属
SELECT e.emp_id, e.emp_name, e.manager_id, oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
)
SELECT emp_id, emp_name, level
FROM org_hierarchy
ORDER BY level, emp_name;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511952.png" /></p>
<p class="maodian"><a name="_label6"></a></p><h2>七、复合查询实战案例</h2>
<p class="maodian"><a name="_lab2_6_14"></a></p><h3>7.1 多层级数据分析</h3>
<div class="jb51code"><pre class="brush:sql;">-- 分析各部门项目参与情况
WITH dept_stats AS (
SELECT d.dept_id, d.dept_name,
COUNT(DISTINCT e.emp_id) as total_emps,
COUNT(DISTINCT ep.emp_id) as project_emps,
COUNT(DISTINCT p.project_id) as project_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
LEFT JOIN emp_projects ep ON e.emp_id = ep.emp_id
LEFT JOIN projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_id, d.dept_name
)
SELECT dept_name,
total_emps,
project_emps,
project_count,
CONCAT(ROUND(project_emps/total_emps*100, 2), '%') AS participation_rate
FROM dept_stats
ORDER BY participation_rate DESC;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511955.jpg" /></p>
<p class="maodian"><a name="_lab2_6_15"></a></p><h3>7.2 复杂业务逻辑实现</h3>
<div class="jb51code"><pre class="brush:sql;">-- 找出每个部门薪资高于部门平均且参与项目的员工
WITH dept_avg_salary AS (
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
),
project_employees AS (
SELECT DISTINCT emp_id
FROM emp_projects
)
SELECT e.emp_name, e.salary, d.dept_name, das.avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN dept_avg_salary das ON e.dept_id = das.dept_id
JOIN project_employees pe ON e.emp_id = pe.emp_id
WHERE e.salary > das.avg_salary
ORDER BY e.dept_id, e.salary DESC;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511958.jpg" /></p>
<p class="maodian"><a name="_label7"></a></p><h2>八、性能优化与最佳实践</h2>
<p class="maodian"><a name="_lab2_7_16"></a></p><h3>8.1 复合查询性能优化</h3>
<p><strong>EXPLAIN分析工具</strong></p>
<div class="jb51code"><pre class="brush:sql;">EXPLAIN
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 15000;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511978.png" /></p>
<ul><li><strong>索引优化建议</strong>
<ul><li>为所有连接条件创建索引</li><li>为WHERE子句中的条件列创建索引</li><li>考虑复合索引的顺序</li></ul></li></ul>
<p><strong>查询重写技巧</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 不推荐：使用HAVING过滤分组前数据
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
HAVING dept_id IN (1, 2, 3);
-- 推荐：在WHERE子句中提前过滤
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
WHERE dept_id IN (1, 2, 3)
GROUP BY dept_id;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511925.png" /></p>
<p class="maodian"><a name="_lab2_7_17"></a></p><h3>8.2 复合查询最佳实践</h3>
<ul><li><strong>保持查询简洁</strong>：避免过度复杂的嵌套</li><li><strong>合理使用注释</strong>：解释复杂查询的逻辑</li><li><strong>分步构建查询</strong>：先测试子查询再组合</li><li><strong>考虑使用视图</strong>：对常用复杂查询创建视图</li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE VIEW dept_project_stats AS
SELECT d.dept_id, d.dept_name,
COUNT(DISTINCT e.emp_id) as emp_count,
COUNT(DISTINCT p.project_id) as project_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
LEFT JOIN projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_id, d.dept_name;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511937.png" /></p>
<p class="maodian"><a name="_label8"></a></p><h2>九、常见问题与解决方案</h2>
<p class="maodian"><a name="_lab2_8_18"></a></p><h3>9.1 性能问题排查</h3>
<p><strong>问题</strong>：复合查询执行缓慢</p>
<p><strong>解决方案</strong>：</p>
<ul><li>使用EXPLAIN分析执行计划</li><li>检查是否使用了适当的索引</li><li>考虑将复杂查询拆分为多个简单查询</li><li>评估是否可以使用临时表存储中间结果</li></ul>
<p class="maodian"><a name="_lab2_8_19"></a></p><h3>9.2 结果不符合预期</h3>
<p><strong>问题</strong>：查询返回的行数多于或少于预期</p>
<p><strong>解决方案</strong>：</p>
<ul><li>检查连接条件是否正确</li><li>确认使用正确的JOIN类型(INNER/LEFT/RIGHT)</li><li>验证WHERE条件逻辑</li><li>检查NULL值的处理方式</li></ul>
<p class="maodian"><a name="_lab2_8_20"></a></p><h3>9.3 语法错误处理</h3>
<p><strong>常见错误</strong>：</p>
<ul><li>子查询返回多行但使用了比较运算符</li><li>在GROUP BY或HAVING中引用了不存在的列</li><li>UNION查询的列数或类型不匹配</li></ul>
<p><strong>解决方案</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 错误示例：子查询返回多行
SELECT emp_name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 1);
-- 正确修改：
SELECT emp_name FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE dept_id = 1);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121809511978.png" /></p>
<p class="maodian"><a name="_label9"></a></p><h2>十、总结与进阶学习建议</h2>
<p class="maodian"><a name="_lab2_9_21"></a></p><h3>10.1 复合查询核心要点总结</h3>
<ul><li>子查询适合解决分步查询问题，但要注意性能</li><li>连接查询是处理表关系的强大工具</li><li>UNION提供了垂直合并结果集的能力</li><li>CTE提高了复杂查询的可读性和可维护性</li></ul>
<p class="maodian"><a name="_lab2_9_22"></a></p><h3>10.2 进阶学习建议</h3>
<ul><li><strong>深入学习执行计划</strong>：掌握EXPLAIN输出解读</li><li><strong>了解查询优化器原理</strong>：学习MySQL如何优化查询</li><li><strong>研究分区表查询</strong>：大数据量下的查询优化</li><li><strong>学习窗口函数</strong>：MySQL 8.0+的高级分析功能</li></ul>
<p>以上就是关于MySQL查询中的所有相关知识点，除了前面常用的外，后面的有些时候并不一定能用到，但都是有必要掌握的，由于篇幅原因，有些问题并不能全面刨析到，建议大家看到不理解的地方可以再去找一些教学视频看一下</p>
頁:
[1]