递归with表达式用于处理层级结构数据，有两种写法。一是基本递归，包含锚定成员和递归成员，适用于单根层级结构；二是多锚点递归，包含多个锚定成员，适用于多根层级结构。优化技巧包括限制递归深度、使用索引、避免不必要的计算、使用物化视图。应用场景有网络拓扑分析、社交网络分析、权限管理和供应链管理。与临时表相比，with表达式作用域和生命周期更短，性能更好，语法更简洁。选择依据是中间结果的使用范围和存储需求。
WITH 表达式，说白了，就是SQL里的“临时表”。但它比临时表更灵活，也更强大。它能让你在查询中定义临时的、命名的结果集，然后在主查询中引用它们。这让复杂的SQL变得更易读、更易维护。而递归 WITH，则是解决层级结构数据的利器。
WITH 表达式主要有两种用法：非递归和递归。这里重点说说递归，因为它更考验理解。
递归 WITH 表达式的两种写法
递归 WITH 表达式的核心在于：它允许一个 WITH 子句引用它自身。这就像一个函数调用自身一样，可以用来处理层级数据，比如组织结构、树形菜单、供应链等等。
写法一：基本递归
这种写法是最常见的，也比较容易理解。它通常包含两个部分：
锚定成员 (Anchor Member): 这是递归的起点，它定义了递归的初始结果集。它就像树的根节点。递归成员 (Recursive Member): 这是递归的部分，它引用 WITH 子句自身，并定义了如何从上一次迭代的结果集中生成新的结果集。它就像树的枝干。
WITH RECURSIVE employee_hierarchy AS ( -- 锚定成员：找到所有顶级员工（没有上级） SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员：找到所有下级员工 SELECT e.id, e.name, e.manager_id, eh.level + 1 AS level FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id)SELECT * FROM employee_hierarchy;
这段代码会查询一个名为 employees 的表，并构建一个员工层级结构。锚定成员找到所有没有上级（manager_id IS NULL）的员工，作为层级的起点。递归成员则通过 JOIN 将每个员工与其上级关联起来，并递增层级 level。
写法二：多锚点递归
Replit Ghostwrite
一种基于 ML 的工具，可提供代码完成、生成、转换和编辑器内搜索功能。
93 查看详情
有时候，你的层级结构可能不是单根的，而是有多个根节点。这时候，就需要使用多锚点递归。
WITH RECURSIVE parts_explosion AS ( -- 锚定成员1：找到所有最终产品（没有零件组成它们） SELECT part_id, part_name, 1 AS level FROM parts WHERE is_final_product = TRUE UNION ALL -- 锚定成员2：找到所有原材料（没有子零件） SELECT part_id, part_name, 1 AS level FROM parts WHERE is_raw_material = TRUE UNION ALL -- 递归成员：找到所有零件的组成部分 SELECT p.part_id, p.part_name, pe.level + 1 AS level FROM parts p JOIN parts_explosion pe ON p.parent_part_id = pe.part_id)SELECT * FROM parts_explosion;
在这个例子中，我们假设有一个 parts 表，其中包含零件的信息，以及零件之间的组成关系 (parent_part_id)。 这个例子有两个锚定成员：最终产品和原材料。递归成员则通过 parent_part_id 将零件与其组成部分关联起来。 这种写法适用于那些有多个起始点的层级结构。
如何优化 WITH 递归表达式的性能？
递归查询通常性能较差，特别是当数据量很大或者层级很深时。以下是一些优化技巧：
限制递归深度: 使用 LIMIT 或者 WHERE 子句来限制递归的深度，避免无限递归。使用索引: 确保在连接列（比如 manager_id 或者 parent_part_id）上创建了索引。避免不必要的计算: 在递归成员中，尽量减少不必要的计算，只计算需要的信息。使用物化视图: 如果递归查询的结果经常被使用，可以考虑创建一个物化视图来缓存结果。
WITH 表达式在实际项目中的应用场景有哪些？
除了上面提到的组织结构和零件组成，WITH 表达式还可以用于：
网络拓扑分析: 查找网络中两个节点之间的所有路径。社交网络分析: 查找用户之间的共同好友。权限管理: 查找用户拥有的所有权限，包括直接权限和继承权限。供应链管理: 跟踪产品的整个生产流程。
WITH 表达式和临时表有什么区别？
虽然 WITH 表达式和临时表都可以用来存储中间结果，但它们之间还是有一些区别：
作用域: WITH 表达式的作用域仅限于当前查询，而临时表可以在多个查询中使用。生命周期: WITH 表达式的生命周期仅限于当前查询的执行时间，而临时表可以在会话期间存在。性能: WITH 表达式通常比临时表更快，因为它可以被优化器更好地优化。语法: WITH 表达式的语法更简洁，更易读。
选择使用 WITH 表达式还是临时表，取决于你的具体需求。如果只需要在单个查询中使用中间结果，并且希望获得更好的性能，那么 WITH 表达式是更好的选择。如果需要在多个查询中使用中间结果，或者需要长期存储中间结果，那么临时表是更好的选择。
以上就是sql中with怎么使用 WITH临时表达式的2种递归写法的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1060083.html
微信扫一扫 
支付宝扫一扫 
