SQL经典案例之数据库的CTE递归循环使用

SQL经典案例之数据库的CTE递归循环使用CTECommon Table ExpressionCTE的基本语法生成数字序列递归的终止CTECommon Table Expression数据库的CTECommon Table Expression公用表表达式是一种在SQL查询中用于定义临时结果集的工具「临时表或称子查询」CTE的基本语法WITHcte_nameAS(SELECT...FROM...WHERE...)-- 使用你定义的CTESELECT*FROMcte_name;案例理解子查询1获取数值 1 子查询2获取数值 2改成t1表和t2表子查询再改用CTE写法的t1和t2临时表会更直观方便理解-- Oracleselectsum((select1asn1fromdual)(select2asn2fromdual))fromdual;selectsum(t1.n1t2.n2)from(select1asn1fromdual)t1,(select2asn2fromdual)t2;witht1as(select1asn1fromdual),t2as(select2asn2fromdual)selectsum(t1.n1t2.n2)fromt1,t2;-- PostgreSQLselectsum((select1asn1)(select2asn2));selectsum(t1.n1t2.n2)from(select1asn1)t1,(select2asn2)t2;witht1as(select1asn1),t2as(select2asn2)selectsum(t1.n1t2.n2)fromt1,t2;关键字 RECURSIVE 表示递归形式的CTE即递归CTEOracle 和 SQL Serer不支持关键字 RECURSIVE直接使用WITH定义递归形式的CTE且必须使用 UNION ALL 运算符SQLite可以省略关键字 RECURSIVEWITH[RECURSIVE]cte_nameAS(-- 初始查询锚点成员/顶层根节点SELECT...FROM...WHERE...UNION[ALL]-- 递归查询递归成员/递归遍历子层SELECT...FROMcte_nameWHERE...;)-- 使用你定义的CTESELECT*FROMcte_name;生成数字序列-- MySQL、PostgreSQL、SQLiteWITHRECURSIVE t_serial(n)as(select1unionallselectn1fromt_serialwheren10)selectnfromt_serial;-- OracleWITHt_serial(n)as(select1fromdualunionallselectn1fromt_serialwheren10)selectnfromt_serial;递归的终止确保递归有明确的终止条件以避免无限递归。递归深度可能会受到数据库系统的限制因此在实际应用中可能需要考虑性能问题和潜在的递归深度限制。递归CTE在处理大型数据集时可能会非常耗时和占用大量内存因此在使用时需要谨慎评估其对系统性能的影响。生成数字序列的例子去掉where条件让递归无终止条件陷入死循环测试-- MySQL、PostgreSQL、SQLiteWITHRECURSIVE t_serial(n)as(select1unionallselectn1fromt_serial)selectnfromt_serial;-- OracleWITHt_serial(n)as(select1fromdualunionallselectn1fromt_serial)selectnfromt_serial;MySQL默认递归1000次由系统变量cte_max_recursion_depth控制后终止并提示错误PostgreSQL和SQLite无限制进入死循环Oracle能检查查询语句中的死循环问题并提示错误SQL Server默认递归100次可在查询中使用MAXRECURSION选项进行设置后终止并提示错误发现了一个前沿巨牛的宝藏人工智能学习网站通俗易懂风趣幽默忍不住给大家分享一下。戳一下跳转到学习