让SQL更优雅！深入浅出【公用表表达式（CTE）】语法及实战案例

开篇语

哈喽，各位小伙伴们，你们好呀，我是喵手。运营社区：C站/掘金/腾讯云/阿里云/华为云/51CTO；欢迎大家常来逛逛

  今天我要给大家分享一些自己日常学习到的一些知识点，并以文字的形式跟大家一起交流，互相学习，一个人虽可以走的更快，但一群人可以走的更远。

  我是一名后端开发爱好者，工作日常接触到最多的就是Java语言啦，所以我都尽量抽业余时间把自己所学到所会的，通过文章的形式进行输出，希望以这种方式帮助到更多的初学者或者想入门的小伙伴们，同时也能对自己的技术进行沉淀，加以复盘，查缺补漏。

小伙伴们在批阅的过程中，如果觉得文章不错，欢迎点赞、收藏、关注哦。三连即是对作者我写作道路上最好的鼓励与支持！

🌟 前言

Hello，各位数据库玩家们！在SQL查询中，我们有时会写出长到让人窒息的查询语句，越往下读，脑子越容易变成一团浆糊……有没有想过，有没有更简洁、更优雅的方式来写出复杂查询？答案就是今天的主角：公用表表达式（CTE，Common Table Expressions）。如果你对SQL优化、可读性提升以及代码复用有兴趣，那就别走开，接下来我们一起探索CTE的魅力！🤩

📜 目录

1. 💡 什么是CTE？
2. 🎨 CTE的语法与结构
3. 💥 使用场景：CTE何时更香？
4. 🎬 CTE实战案例
5. 🧩 递归CTE：挑战升级
6. 🛠️ CTE与子查询的比较
7. 🚀 总结

---

💡 什么是CTE？

公用表表达式（CTE）其实是SQL语句中的“临时表”，定义后可以在同一个查询中多次使用。相比传统子查询，CTE让代码更简洁、结构更清晰。CTE的出现就是为了提升SQL查询的可读性，特别是对于复杂查询场景，CTE简直是救世主！🙏

简而言之，CTE是SQL代码中的“局部变量”，你可以用它来暂时存储中间结果，就像在打游戏时暂存进度一样。

🎨 CTE的语法与结构

写CTE其实很简单，来个大体结构先：

WITH cte_name AS (-- 这里放查询逻辑SELECT column1, column2FROM your_tableWHERE conditions
)
SELECT * FROM cte_name;

基本语法分为三步：

1. 使用关键字WITH引入CTE。
2. 起个名字，比如cte_name，方便后续调用。
3. 在CTE内写SQL查询，随后在主查询中使用。

是不是挺轻松？现在，让我们进入更有趣的实战环节吧！💪

💥 使用场景：CTE何时更香？

CTE非常适合以下场景：

• 多步查询的中间结果：如果查询逻辑复杂，有多步计算的需求，可以用CTE来清晰地表达每一步。
• 递归查询：想从某个父节点找到所有子节点？使用递归CTE非常合适。
• 代码复用：在一个查询中多次用到同一中间表时，CTE比子查询更直观。

小提示：CTE并不是性能优化的“灵丹妙药”，更多的是一种结构优化。所以有些场景下，CTE可能会提高性能，但更多时候，它的作用是提升代码的可读性。

🎬 CTE实战案例

案例1：统计每个部门的平均薪资

假设我们有一张员工表，包含员工姓名、部门ID和薪资信息。我们想统计每个部门的平均薪资，最基础的写法可能是这样的：

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

不过，假设我们还想加入一些复杂的筛选条件和分组逻辑，这时可以使用CTE来优化代码的结构：

WITH department_avg AS (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
)
SELECT d.department_id, e.employee_name, department_avg.avg_salary
FROM employees e
JOIN department_avg d ON e.department_id = d.department_id
WHERE e.salary > department_avg.avg_salary;

在这个例子中，我们先通过CTE计算出每个部门的平均薪资，再通过主查询对比员工的薪资是否高于部门平均值。这样分步骤编写，逻辑一目了然。🔍

案例2：递归查询——公司架构层级

递归CTE也是非常经典的应用场景。假设我们有一张表记录了员工与上级的关系（employee_id、manager_id）。如果我们想查看某个员工的所有上下级关系，就可以使用递归CTE来解决这个问题。

WITH RECURSIVE org_chart AS (SELECT employee_id, manager_id, 1 AS levelFROM employeesWHERE manager_id IS NULL -- 找到顶层管理者UNION ALLSELECT e.employee_id, e.manager_id, org_chart.level + 1FROM employees eINNER JOIN org_chart ON e.manager_id = org_chart.employee_id
)
SELECT * FROM org_chart;

这里的递归CTE通过自连接实现层级递归，将顶层管理者的所有下属关系输出。这个查询语句让人耳目一新，使用递归CTE后，复杂的上下级关系链条也变得很清晰。👨‍💼

🧩 递归CTE：挑战升级

递归CTE不仅能用来处理员工层级关系，还能应对例如路径分析、数列生成等场景。递归CTE的核心在于自我调用，但也要小心使用，避免形成“死循环”。

提示：递归CTE默认会有100层的递归限制，避免无限循环。使用时建议给递归CTE的层数设限，以提高查询的稳定性。

🛠️ CTE与子查询的比较

很多同学会问，CTE和子查询到底有啥不同？其实两者都能实现类似的效果，但在代码结构上，CTE往往更直观、易读。以下是两者的对比：

特点	CTE	子查询
可读性	👍 易读	👎 略复杂
重用性	👍 可以在主查询多次调用	👎 通常只能使用一次
性能优化	👎 有时会增加性能开销	👍 有时更快
适用场景	复杂查询、递归查询	简单查询

📌 小贴士：如果查询比较简单，建议使用子查询；如果查询逻辑较复杂且需要复用中间结果，CTE则是更好的选择。

🚀 总结

CTE带来的不仅仅是SQL结构上的优化，更是一种“代码洁癖”式的编程体验。通过CTE，你的SQL查询代码会更加整洁、可读性更强。在合适的场景下使用CTE，代码不仅不会失去性能优势，反而更容易维护、优化。

在下次编写复杂SQL查询时，记得考虑CTE哦，它会帮你提升代码的美观度和可读性！希望这篇文章对你理解CTE有所帮助，祝大家写SQL越来越顺手！🖖

… …

文末

好啦，以上就是我这期的全部内容，如果有任何疑问，欢迎下方留言哦，咱们下期见。

… …

学习不分先后，知识不分多少；事无巨细，当以虚心求教；三人行，必有我师焉！！！

wished for you successed ！！！

---

⭐️若喜欢我，就请关注我叭。

⭐️若对您有用，就请点赞叭。

⭐️若有疑问，就请评论留言告诉我叭。