claude-skills SQL Pro 实战掌握 CTE、递归查询与高级 JOIN 模式的完整查询模式手册【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills导读claude-skills仓库为 Claude Code 提供了 67 个专家级技能Skills其中 sql-pro 面向 SQL 查询优化、数据库表结构设计与性能调优场景。本文以该技能的核心参考文档 query-patterns.md 为主体系统讲解通用表表达式CTE、递归 CTE、高级 JOIN、子查询优化、PIVOT/UNPIVOT 与集合运算等高频查询模式并结合仓库中 window-functions.md、optimization.md、database-design.md 等参考文档做纵深补充。读完本文你将能直接复制这些可运行的模式代码理解其背后的优化原理并把它们套用到自己的业务查询中。为什么查询模式是 SQL Pro 技能的第一参考在 sql-pro 技能的 SKILL.md 中Query Patterns 被列为五个参考主题之首其触发场景覆盖 JOINs、CTEs、子查询、递归查询。该技能的 Core Workflow 给出了一条清晰的链路Schema Analysis分析表结构与瓶颈→ Design用集合化思路设计查询→ Optimize结合执行计划与索引优化→ VerifyEXPLAIN ANALYZE 验证→ Document沉淀可复用的查询与索引。其中 Design 环节明确要求使用 CTEs、窗口函数、合适的 JOIN 创建集合化操作这正是本参考文档的用武之地。仓库中还保留了一条有趣的实测记录在 SKILL_TRIGGER_LOGS.md 中输入 optimize this PostgreSQL query 时模型并未被期望触发 sql-pro 技能——这说明正确的查询模式参考不仅关乎 SQL 语法本身更要求开发者或 Agent主动加载对应参考文档。而 SKILLS_GUIDE.md 对 SQL Pro 的定位是高级 SQL、查询优化、窗口函数、CTE与本文主题完全吻合。Common Table ExpressionsCTE基础模式CTEWITH 子句是提升复杂查询可读性与可复用性的第一工具。query-patterns.md 给出了两个典型场景。模式一拆分逻辑、先过滤后聚合第一个示例将活跃用户与用户订单统计分别封装为独立 CTE再通过 LEFT JOIN 组装得到每位活跃用户的订单数与终身价值WITH active_users AS ( SELECT user_id, username, created_at FROM users WHERE is_active true AND last_login CURRENT_DATE - INTERVAL 30 days ), user_orders AS ( SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent FROM orders WHERE status completed GROUP BY user_id ) SELECT u.username, u.created_at, COALESCE(o.order_count, 0) as orders, COALESCE(o.total_spent, 0) as lifetime_value FROM active_users u LEFT JOIN user_orders o ON u.user_id o.user_id WHERE COALESCE(o.order_count, 0) 0 ORDER BY o.total_spent DESC;注意两个关键细节先过滤再进入 CTEWHERE is_active true与WHERE status completed都在 CTE 内部完成缩小了后续 JOIN 的输入规模。这与 sql-pro SKILL.md 的 MUST DO 约束Apply filtering early in query execution尽早应用过滤条件完全一致。显式处理 NULLLEFT JOIN 后没有订单的用户order_count/total_spent为 NULL用COALESCE(..., 0)归一化避免出现 NULL 参与排序或计算。SKILL.md 同样要求Handle NULLs explicitly in comparisons and aggregations。模式二同一 CTE 被多次引用CTE 可以像临时视图一样被多次引用避免重复编写同一段聚合逻辑。下面的月销售对比把monthly_sales自连接两次计算每个产品当月相对上月的增长额与增长率WITH monthly_sales AS ( SELECT DATE_TRUNC(month, sale_date) as month, product_id, SUM(quantity) as total_quantity, SUM(amount) as total_amount FROM sales WHERE sale_date 2024-01-01 GROUP BY DATE_TRUNC(month, sale_date), product_id ) SELECT current.month, current.product_id, current.total_amount, current.total_amount - COALESCE(previous.total_amount, 0) as growth, ROUND(100.0 * (current.total_amount - COALESCE(previous.total_amount, 0)) / NULLIF(previous.total_amount, 0), 2) as growth_pct FROM monthly_sales current LEFT JOIN monthly_sales previous ON current.product_id previous.product_id AND current.month previous.month INTERVAL 1 month;这里NULLIF(previous.total_amount, 0)是防止除零的关键手段上月没有销售记录时除数为 NULLgrowth_pct结果为 NULL 而非报错。LEFT JOIN配合current.month previous.month INTERVAL 1 month实现了标准的同比环比取值比逐行子查询高效得多。实战提示query-patterns.md 的 Performance Tips 第 1 条提醒——PostgreSQL 12 默认会物化materializeCTE即把 CTE 结果落成临时结果集。如果你的 CTE 只需执行一次或希望内联展开可显式使用WITH cte AS NOT MATERIALIZED反之若想强制缓存多次引用的计算结果则用WITH cte AS MATERIALIZED。写复杂查询时物化行为会影响最终执行计划务必通过 EXPLAIN 验证。Recursive CTE处理树形与图结构数据当数据呈层级关系组织架构、BOM 物料清单、分类树时递归 CTE 是最简洁的解法。其语法由两部分构成Anchor member锚点成员非递归的初始结果集通常是顶层节点Recursive member递归成员引用 CTE 自身继续向下扩展通过UNION ALL与锚点合并。组织架构层级遍历WITH RECURSIVE org_hierarchy AS ( -- Anchor member: top-level managers SELECT employee_id, name, manager_id, 1 as level, ARRAY[employee_id] as path, name as hierarchy_path FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive member: employees reporting to current level SELECT e.employee_id, e.name, e.manager_id, h.level 1, h.path || e.employee_id, h.hierarchy_path || || e.name FROM employees e INNER JOIN org_hierarchy h ON e.manager_id h.employee_id WHERE NOT e.employee_id ANY(h.path) -- Prevent cycles ) SELECT employee_id, REPEAT( , level - 1) || name as indented_name, level, hierarchy_path FROM org_hierarchy ORDER BY path;这段代码演示了三个递归查询的工程要点层级计数level字段逐层递增便于后续按层级缩进展示REPEAT( , level - 1)路径追踪path数组记录从根到当前节点的完整 ID 链路hierarchy_path保存可读的 A B C 文本防环WHERE NOT e.employee_id ANY(h.path)确保同一员工不会被重复收入路径防止数据异常时无限递归。递归 CTE 默认有最大深度限制生产环境建议结合max_recursive_iterations等参数做兜底。BOM 物料清单零件爆炸第二个示例演示递归 CTE 在制造业 BOM 中的应用从成品出发沿着bill_of_materials表逐层展开组件并把各层数量相乘得到累计用量WITH RECURSIVE parts_explosion AS ( SELECT part_id, component_id, quantity, 1 as level, ARRAY[part_id] as path FROM bill_of_materials WHERE part_id PRODUCT-123 UNION ALL SELECT pe.part_id, bom.component_id, pe.quantity * bom.quantity, pe.level 1, pe.path || bom.part_id FROM parts_explosion pe INNER JOIN bill_of_materials bom ON pe.component_id bom.part_id WHERE NOT bom.part_id ANY(pe.path) ) SELECT component_id, SUM(quantity) as total_quantity, MAX(level) as max_depth FROM parts_explosion GROUP BY component_id;数量相乘pe.quantity * bom.quantity是 BOM 展开的核心每个下层组件的数量等于路径上所有层级数量的乘积。最后按组件分组汇总得到整个产品所需各组件总量与最大嵌套深度。跨方言提醒递归 CTE 并非所有数据库写法一致。dialect-differences.md 中专门对比了四种主流数据库——PostgreSQL 与 MySQL 8.0 使用WITH RECURSIVE关键词SQL Server 省略RECURSIVE直接写WITH ... UNION ALL ...而 Oracle 更传统使用CONNECT BY PRIOR语法完成同样的层级查询。跨库迁移时必须改写这部分语法。高级 JOIN 模式自连接、LATERAL 与反连接query-patterns.md 给出了四类容易被忽略但极具实战价值的 JOIN 技巧。自连接查找序列中的缺口SELECT a.order_id as current_id, MIN(b.order_id) as next_id, MIN(b.order_id) - a.order_id - 1 as gap_size FROM orders a LEFT JOIN orders b ON b.order_id a.order_id GROUP BY a.order_id HAVING MIN(b.order_id) - a.order_id 1;自连接把同一张表当作两张表使用对每个a.order_id找出所有比它大的订单号中的最小值两者差值减 1 就是中间的缺口大小。HAVING ... 1过滤掉连续无缺口的行。这种模式常用于订单号、票号等序列完整性的审计。LATERAL 连接每行计算相关子查询PostgreSQLSELECT c.customer_id, c.name, recent.order_date, recent.total FROM customers c CROSS JOIN LATERAL ( SELECT order_date, total FROM orders o WHERE o.customer_id c.customer_id ORDER BY order_date DESC LIMIT 3 ) recent;LATERAL允许子查询引用外部查询的列此处是c.customer_id实现对每行取该客户最近 3 笔订单这类相关子查询的优雅表达。相比在 SELECT 子句中写标量子查询LATERAL 只执行必要的扫描量可读性和性能都更好。反连接Anti-JoinA 中存在但 B 中不存在的记录找出从未下过单的用户是反连接最典型的需求文档给出了两种等价写法-- LEFT JOIN IS NULL 过滤 SELECT u.user_id, u.email FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.order_id IS NULL; -- NOT EXISTS大集合场景下通常更高效 SELECT u.user_id, u.email FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );文档特别提示当右表集合较大时EXISTS/NOT EXISTS通常比IN/NOT IN更高效因为 EXISTS 只要找到第一条匹配即可短路返回而 IN 需要先物化完整子查询结果集。此外NOT IN在子查询结果含 NULL 时会产生语义陷阱整条 WHERE 失效这也是 sql-pro 的优化参考 optimization.md 中明确建议用 NOT EXISTS 替代 NOT IN的原因。子查询优化从 N1 到集合化标量子查询的 N1 陷阱在 SELECT 子句中使用相关标量子查询会为外层每一行执行一次子查询即 N1 问题-- 尽量避免每个产品行都要单独跑两次子查询 SELECT p.product_id, p.name, (SELECT COUNT(*) FROM reviews r WHERE r.product_id p.product_id) as review_count, (SELECT AVG(rating) FROM reviews r WHERE r.product_id p.product_id) as avg_rating FROM products p;更优先聚合再 LEFT JOINSELECT p.product_id, p.name, COALESCE(r.review_count, 0) as review_count, r.avg_rating FROM products p LEFT JOIN ( SELECT product_id, COUNT(*) as review_count, AVG(rating) as avg_rating FROM reviews GROUP BY product_id ) r ON p.product_id r.product_id;思路核心把N 次小查询压缩为一次 GROUP BY 聚合 一次 JOIN。这也是 SKILL.md 中 Before/After 优化示例correlated subquery → single aggregation join所演示的同款模式该文档还配套给出了支撑查询的覆盖索引建议CREATE INDEX idx_order_items_order_qty ON order_items (order_id) INCLUDE (quantity);相关子查询 vs 窗口函数当业务是找出高于该客户平均订单金额的订单时相关子查询需要逐客户计算平均写起来既绕又慢-- 相关子查询版本 SELECT order_id, customer_id, total FROM orders o1 WHERE total ( SELECT AVG(total) FROM orders o2 WHERE o2.customer_id o1.customer_id );更优的写法是利用窗口函数一次性算出每个客户的均值SELECT order_id, customer_id, total FROM ( SELECT order_id, customer_id, total, AVG(total) OVER (PARTITION BY customer_id) as avg_customer_total FROM orders ) x WHERE total avg_customer_total;窗口函数AVG(...) OVER (PARTITION BY customer_id)在单次扫描中为每行计算所属分区的均值完全不依赖相关子查询的逐行执行。关于 ROW_NUMBER、RANK、LAG/LEAD、FIRST_VALUE 以及 ROWS/RANGE 帧规范的完整用法可进一步阅读同目录的 window-functions.md——其中包括用LAG()做会话切分、用NTILE(4)做四分位分桶、用generate_series做时间序列缺口填充等进阶内容与本模式的相关子查询 → 窗口函数优化思路一脉相承。PIVOT / UNPIVOT行转列与列转行PostgreSQL CROSSTAB需要 tablefunc 扩展CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM crosstab( SELECT customer_id, product_category, SUM(amount) FROM sales GROUP BY customer_id, product_category ORDER BY customer_id, product_category, SELECT DISTINCT product_category FROM sales ORDER BY 1 ) AS ct(customer_id INT, electronics NUMERIC, clothing NUMERIC, food NUMERIC);crosstab函数接受两个文本参数第一个是产生 (行键, 列键, 值) 三元组的查询第二个是列键的去重有序列表。输出列的个数与类型必须与列键查询的返回行一一对应写错会导致运行时报错。手动 PIVOTCASE 聚合不依赖扩展、各数据库通用的做法是用条件聚合SELECT customer_id, SUM(CASE WHEN product_category electronics THEN amount ELSE 0 END) as electronics, SUM(CASE WHEN product_category clothing THEN amount ELSE 0 END) as clothing, SUM(CASE WHEN product_category food THEN amount ELSE 0 END) as food FROM sales GROUP BY customer_id;它的局限在于列是写死的新增一个品类就需要手动加一列。因此它适合品类数量少且稳定的报表场景而 CROSSTAB 适合品类动态变化的场景。UNPIVOT列转行反方向的 UNPIVOT 没有原生函数用UNION ALL逐列展开即可SELECT customer_id, electronics as category, electronics as amount FROM customer_sales WHERE electronics 0 UNION ALL SELECT customer_id, clothing, clothing FROM customer_sales WHERE clothing 0 UNION ALL SELECT customer_id, food, food FROM customer_sales WHERE food 0;WHERE electronics 0等条件用于跳过空值/零值避免生成冗余行。注意UNION ALL与UNION的区别ALL 保留重复行、无去重开销语义更准确同一客户的三个品类互不重叠性能也更好——这正是 query-patterns.md 性能提示第 5 条所强调的。集合运算UNION / INTERSECT / EXCEPT集合运算符用于在行维度合并或比较两个查询的结果集-- UNION去重合并取两个表的产品并集 SELECT product_id FROM active_products UNION SELECT product_id FROM featured_products; -- UNION ALL保留重复行适合事件流合并场景 SELECT user_id, signup as event FROM signups WHERE date CURRENT_DATE UNION ALL SELECT user_id, purchase as event FROM purchases WHERE date CURRENT_DATE; -- INTERSECT两表共有的邮箱 SELECT email FROM newsletter_subscribers INTERSECT SELECT email FROM premium_members; -- EXCEPT在 A 中但不在 B 中的邮箱 SELECT email FROM all_users EXCEPT SELECT email FROM unsubscribed_users;四个运算符的语义分别是UNION并集去重、UNION ALL并集不去重、INTERSECT交集、EXCEPT差集。它们要求两侧结果集的列数相同且对应列类型兼容。当业务目标是是否存在/是否相同这类判断时应优先考虑 EXISTS 而非集合运算后者需要完整物化两侧结果。性能速查五条核心原则query-patterns.md 末尾的 Performance Tips 是全文精华结合仓库中 optimization.md 可以归纳为五条可执行的检查清单CTE 物化控制PostgreSQL 12 默认物化 CTE用WITH cte AS MATERIALIZED或NOT MATERIALIZED显式控制避免不必要的临时结果集写入。这在 CTE 只被引用一次或内联更优时尤其重要。JOIN 顺序现代优化器如 PostgreSQL 的基于代价优化器会自行选择连接顺序手动优化时把较小的表放在前面通常更利于嵌套循环连接但最终应以 EXPLAIN 输出为准不要盲目相信经验法则。EXISTS vs IN相关检查用EXISTS短路语义、天然规避 NULL 陷阱小且静态的列表用IN更直观。子查询 vs JOIN优先 JOIN——优化器对 JOIN 的可重写空间更大可读性也更好。这与上文子查询优化一节完全对应。UNION ALL vs UNION重复行可接受时一律用UNION ALL省去去重排序成本。同样SQL Pro 的 MUST NOT DO 还包括生产查询禁用 SELECT *、能用集合操作就不用游标这些约束在 SKILL.md 中有完整罗列。如果要系统验证上述查询是否走索引optimization.md 建议用EXPLAIN (ANALYZE, BUFFERS, VERBOSE)检查 Seq Scan 数量、预估行数与实际行数偏差、Buffer 命中情况SQL Pro 的 SKILL.md 还给出了一个明确的验收标准——EXPLAIN ANALYZE确认大表无全表扫描若查询未达到亚百毫秒目标需迭代索引选择或查询重写。总结如何把查询模式变成日常习惯query-patterns.md 覆盖了从基础 CTE 到递归层级遍历、从反连接到子查询重写、从行列转换到集合运算的完整模式图谱。建议的落地路径是遇到复杂聚合先问自己能不能用 CTE 拆解把过滤提前、聚合复用遇到每行取 Top N先想LATERAL或窗口函数而不是标量子查询涉及存在性判断统一走EXISTS/NOT EXISTS绕开 NULL 陷阱与去重开销每次改写后都用EXPLAIN ANALYZE前后对比结合 optimization.md 的监控查询定位慢 SQL跨数据库迁移前查阅 dialect-differences.md 的四库对照自增主键、字符串拼接、日期函数、LIMIT/OFFSET、UPSERT、数据类型映射等。这些模式与配套的窗口函数、索引设计和方言对照参考共同构成了 sql-pro 技能的完整知识体系既可作为 Claude Code 的上下文输入也可作为开发者日常排查 SQL 性能问题的手册。【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 claude-skills SQL Pro 实战：掌握 CTE、递归查询与高级 JOIN 模式的完整查询模式手册
claude-skills SQL Pro 实战掌握 CTE、递归查询与高级 JOIN 模式的完整查询模式手册【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills导读claude-skills仓库为 Claude Code 提供了 67 个专家级技能Skills其中 sql-pro 面向 SQL 查询优化、数据库表结构设计与性能调优场景。本文以该技能的核心参考文档 query-patterns.md 为主体系统讲解通用表表达式CTE、递归 CTE、高级 JOIN、子查询优化、PIVOT/UNPIVOT 与集合运算等高频查询模式并结合仓库中 window-functions.md、optimization.md、database-design.md 等参考文档做纵深补充。读完本文你将能直接复制这些可运行的模式代码理解其背后的优化原理并把它们套用到自己的业务查询中。为什么查询模式是 SQL Pro 技能的第一参考在 sql-pro 技能的 SKILL.md 中Query Patterns 被列为五个参考主题之首其触发场景覆盖 JOINs、CTEs、子查询、递归查询。该技能的 Core Workflow 给出了一条清晰的链路Schema Analysis分析表结构与瓶颈→ Design用集合化思路设计查询→ Optimize结合执行计划与索引优化→ VerifyEXPLAIN ANALYZE 验证→ Document沉淀可复用的查询与索引。其中 Design 环节明确要求使用 CTEs、窗口函数、合适的 JOIN 创建集合化操作这正是本参考文档的用武之地。仓库中还保留了一条有趣的实测记录在 SKILL_TRIGGER_LOGS.md 中输入 optimize this PostgreSQL query 时模型并未被期望触发 sql-pro 技能——这说明正确的查询模式参考不仅关乎 SQL 语法本身更要求开发者或 Agent主动加载对应参考文档。而 SKILLS_GUIDE.md 对 SQL Pro 的定位是高级 SQL、查询优化、窗口函数、CTE与本文主题完全吻合。Common Table ExpressionsCTE基础模式CTEWITH 子句是提升复杂查询可读性与可复用性的第一工具。query-patterns.md 给出了两个典型场景。模式一拆分逻辑、先过滤后聚合第一个示例将活跃用户与用户订单统计分别封装为独立 CTE再通过 LEFT JOIN 组装得到每位活跃用户的订单数与终身价值WITH active_users AS ( SELECT user_id, username, created_at FROM users WHERE is_active true AND last_login CURRENT_DATE - INTERVAL 30 days ), user_orders AS ( SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent FROM orders WHERE status completed GROUP BY user_id ) SELECT u.username, u.created_at, COALESCE(o.order_count, 0) as orders, COALESCE(o.total_spent, 0) as lifetime_value FROM active_users u LEFT JOIN user_orders o ON u.user_id o.user_id WHERE COALESCE(o.order_count, 0) 0 ORDER BY o.total_spent DESC;注意两个关键细节先过滤再进入 CTEWHERE is_active true与WHERE status completed都在 CTE 内部完成缩小了后续 JOIN 的输入规模。这与 sql-pro SKILL.md 的 MUST DO 约束Apply filtering early in query execution尽早应用过滤条件完全一致。显式处理 NULLLEFT JOIN 后没有订单的用户order_count/total_spent为 NULL用COALESCE(..., 0)归一化避免出现 NULL 参与排序或计算。SKILL.md 同样要求Handle NULLs explicitly in comparisons and aggregations。模式二同一 CTE 被多次引用CTE 可以像临时视图一样被多次引用避免重复编写同一段聚合逻辑。下面的月销售对比把monthly_sales自连接两次计算每个产品当月相对上月的增长额与增长率WITH monthly_sales AS ( SELECT DATE_TRUNC(month, sale_date) as month, product_id, SUM(quantity) as total_quantity, SUM(amount) as total_amount FROM sales WHERE sale_date 2024-01-01 GROUP BY DATE_TRUNC(month, sale_date), product_id ) SELECT current.month, current.product_id, current.total_amount, current.total_amount - COALESCE(previous.total_amount, 0) as growth, ROUND(100.0 * (current.total_amount - COALESCE(previous.total_amount, 0)) / NULLIF(previous.total_amount, 0), 2) as growth_pct FROM monthly_sales current LEFT JOIN monthly_sales previous ON current.product_id previous.product_id AND current.month previous.month INTERVAL 1 month;这里NULLIF(previous.total_amount, 0)是防止除零的关键手段上月没有销售记录时除数为 NULLgrowth_pct结果为 NULL 而非报错。LEFT JOIN配合current.month previous.month INTERVAL 1 month实现了标准的同比环比取值比逐行子查询高效得多。实战提示query-patterns.md 的 Performance Tips 第 1 条提醒——PostgreSQL 12 默认会物化materializeCTE即把 CTE 结果落成临时结果集。如果你的 CTE 只需执行一次或希望内联展开可显式使用WITH cte AS NOT MATERIALIZED反之若想强制缓存多次引用的计算结果则用WITH cte AS MATERIALIZED。写复杂查询时物化行为会影响最终执行计划务必通过 EXPLAIN 验证。Recursive CTE处理树形与图结构数据当数据呈层级关系组织架构、BOM 物料清单、分类树时递归 CTE 是最简洁的解法。其语法由两部分构成Anchor member锚点成员非递归的初始结果集通常是顶层节点Recursive member递归成员引用 CTE 自身继续向下扩展通过UNION ALL与锚点合并。组织架构层级遍历WITH RECURSIVE org_hierarchy AS ( -- Anchor member: top-level managers SELECT employee_id, name, manager_id, 1 as level, ARRAY[employee_id] as path, name as hierarchy_path FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive member: employees reporting to current level SELECT e.employee_id, e.name, e.manager_id, h.level 1, h.path || e.employee_id, h.hierarchy_path || || e.name FROM employees e INNER JOIN org_hierarchy h ON e.manager_id h.employee_id WHERE NOT e.employee_id ANY(h.path) -- Prevent cycles ) SELECT employee_id, REPEAT( , level - 1) || name as indented_name, level, hierarchy_path FROM org_hierarchy ORDER BY path;这段代码演示了三个递归查询的工程要点层级计数level字段逐层递增便于后续按层级缩进展示REPEAT( , level - 1)路径追踪path数组记录从根到当前节点的完整 ID 链路hierarchy_path保存可读的 A B C 文本防环WHERE NOT e.employee_id ANY(h.path)确保同一员工不会被重复收入路径防止数据异常时无限递归。递归 CTE 默认有最大深度限制生产环境建议结合max_recursive_iterations等参数做兜底。BOM 物料清单零件爆炸第二个示例演示递归 CTE 在制造业 BOM 中的应用从成品出发沿着bill_of_materials表逐层展开组件并把各层数量相乘得到累计用量WITH RECURSIVE parts_explosion AS ( SELECT part_id, component_id, quantity, 1 as level, ARRAY[part_id] as path FROM bill_of_materials WHERE part_id PRODUCT-123 UNION ALL SELECT pe.part_id, bom.component_id, pe.quantity * bom.quantity, pe.level 1, pe.path || bom.part_id FROM parts_explosion pe INNER JOIN bill_of_materials bom ON pe.component_id bom.part_id WHERE NOT bom.part_id ANY(pe.path) ) SELECT component_id, SUM(quantity) as total_quantity, MAX(level) as max_depth FROM parts_explosion GROUP BY component_id;数量相乘pe.quantity * bom.quantity是 BOM 展开的核心每个下层组件的数量等于路径上所有层级数量的乘积。最后按组件分组汇总得到整个产品所需各组件总量与最大嵌套深度。跨方言提醒递归 CTE 并非所有数据库写法一致。dialect-differences.md 中专门对比了四种主流数据库——PostgreSQL 与 MySQL 8.0 使用WITH RECURSIVE关键词SQL Server 省略RECURSIVE直接写WITH ... UNION ALL ...而 Oracle 更传统使用CONNECT BY PRIOR语法完成同样的层级查询。跨库迁移时必须改写这部分语法。高级 JOIN 模式自连接、LATERAL 与反连接query-patterns.md 给出了四类容易被忽略但极具实战价值的 JOIN 技巧。自连接查找序列中的缺口SELECT a.order_id as current_id, MIN(b.order_id) as next_id, MIN(b.order_id) - a.order_id - 1 as gap_size FROM orders a LEFT JOIN orders b ON b.order_id a.order_id GROUP BY a.order_id HAVING MIN(b.order_id) - a.order_id 1;自连接把同一张表当作两张表使用对每个a.order_id找出所有比它大的订单号中的最小值两者差值减 1 就是中间的缺口大小。HAVING ... 1过滤掉连续无缺口的行。这种模式常用于订单号、票号等序列完整性的审计。LATERAL 连接每行计算相关子查询PostgreSQLSELECT c.customer_id, c.name, recent.order_date, recent.total FROM customers c CROSS JOIN LATERAL ( SELECT order_date, total FROM orders o WHERE o.customer_id c.customer_id ORDER BY order_date DESC LIMIT 3 ) recent;LATERAL允许子查询引用外部查询的列此处是c.customer_id实现对每行取该客户最近 3 笔订单这类相关子查询的优雅表达。相比在 SELECT 子句中写标量子查询LATERAL 只执行必要的扫描量可读性和性能都更好。反连接Anti-JoinA 中存在但 B 中不存在的记录找出从未下过单的用户是反连接最典型的需求文档给出了两种等价写法-- LEFT JOIN IS NULL 过滤 SELECT u.user_id, u.email FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.order_id IS NULL; -- NOT EXISTS大集合场景下通常更高效 SELECT u.user_id, u.email FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );文档特别提示当右表集合较大时EXISTS/NOT EXISTS通常比IN/NOT IN更高效因为 EXISTS 只要找到第一条匹配即可短路返回而 IN 需要先物化完整子查询结果集。此外NOT IN在子查询结果含 NULL 时会产生语义陷阱整条 WHERE 失效这也是 sql-pro 的优化参考 optimization.md 中明确建议用 NOT EXISTS 替代 NOT IN的原因。子查询优化从 N1 到集合化标量子查询的 N1 陷阱在 SELECT 子句中使用相关标量子查询会为外层每一行执行一次子查询即 N1 问题-- 尽量避免每个产品行都要单独跑两次子查询 SELECT p.product_id, p.name, (SELECT COUNT(*) FROM reviews r WHERE r.product_id p.product_id) as review_count, (SELECT AVG(rating) FROM reviews r WHERE r.product_id p.product_id) as avg_rating FROM products p;更优先聚合再 LEFT JOINSELECT p.product_id, p.name, COALESCE(r.review_count, 0) as review_count, r.avg_rating FROM products p LEFT JOIN ( SELECT product_id, COUNT(*) as review_count, AVG(rating) as avg_rating FROM reviews GROUP BY product_id ) r ON p.product_id r.product_id;思路核心把N 次小查询压缩为一次 GROUP BY 聚合 一次 JOIN。这也是 SKILL.md 中 Before/After 优化示例correlated subquery → single aggregation join所演示的同款模式该文档还配套给出了支撑查询的覆盖索引建议CREATE INDEX idx_order_items_order_qty ON order_items (order_id) INCLUDE (quantity);相关子查询 vs 窗口函数当业务是找出高于该客户平均订单金额的订单时相关子查询需要逐客户计算平均写起来既绕又慢-- 相关子查询版本 SELECT order_id, customer_id, total FROM orders o1 WHERE total ( SELECT AVG(total) FROM orders o2 WHERE o2.customer_id o1.customer_id );更优的写法是利用窗口函数一次性算出每个客户的均值SELECT order_id, customer_id, total FROM ( SELECT order_id, customer_id, total, AVG(total) OVER (PARTITION BY customer_id) as avg_customer_total FROM orders ) x WHERE total avg_customer_total;窗口函数AVG(...) OVER (PARTITION BY customer_id)在单次扫描中为每行计算所属分区的均值完全不依赖相关子查询的逐行执行。关于 ROW_NUMBER、RANK、LAG/LEAD、FIRST_VALUE 以及 ROWS/RANGE 帧规范的完整用法可进一步阅读同目录的 window-functions.md——其中包括用LAG()做会话切分、用NTILE(4)做四分位分桶、用generate_series做时间序列缺口填充等进阶内容与本模式的相关子查询 → 窗口函数优化思路一脉相承。PIVOT / UNPIVOT行转列与列转行PostgreSQL CROSSTAB需要 tablefunc 扩展CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM crosstab( SELECT customer_id, product_category, SUM(amount) FROM sales GROUP BY customer_id, product_category ORDER BY customer_id, product_category, SELECT DISTINCT product_category FROM sales ORDER BY 1 ) AS ct(customer_id INT, electronics NUMERIC, clothing NUMERIC, food NUMERIC);crosstab函数接受两个文本参数第一个是产生 (行键, 列键, 值) 三元组的查询第二个是列键的去重有序列表。输出列的个数与类型必须与列键查询的返回行一一对应写错会导致运行时报错。手动 PIVOTCASE 聚合不依赖扩展、各数据库通用的做法是用条件聚合SELECT customer_id, SUM(CASE WHEN product_category electronics THEN amount ELSE 0 END) as electronics, SUM(CASE WHEN product_category clothing THEN amount ELSE 0 END) as clothing, SUM(CASE WHEN product_category food THEN amount ELSE 0 END) as food FROM sales GROUP BY customer_id;它的局限在于列是写死的新增一个品类就需要手动加一列。因此它适合品类数量少且稳定的报表场景而 CROSSTAB 适合品类动态变化的场景。UNPIVOT列转行反方向的 UNPIVOT 没有原生函数用UNION ALL逐列展开即可SELECT customer_id, electronics as category, electronics as amount FROM customer_sales WHERE electronics 0 UNION ALL SELECT customer_id, clothing, clothing FROM customer_sales WHERE clothing 0 UNION ALL SELECT customer_id, food, food FROM customer_sales WHERE food 0;WHERE electronics 0等条件用于跳过空值/零值避免生成冗余行。注意UNION ALL与UNION的区别ALL 保留重复行、无去重开销语义更准确同一客户的三个品类互不重叠性能也更好——这正是 query-patterns.md 性能提示第 5 条所强调的。集合运算UNION / INTERSECT / EXCEPT集合运算符用于在行维度合并或比较两个查询的结果集-- UNION去重合并取两个表的产品并集 SELECT product_id FROM active_products UNION SELECT product_id FROM featured_products; -- UNION ALL保留重复行适合事件流合并场景 SELECT user_id, signup as event FROM signups WHERE date CURRENT_DATE UNION ALL SELECT user_id, purchase as event FROM purchases WHERE date CURRENT_DATE; -- INTERSECT两表共有的邮箱 SELECT email FROM newsletter_subscribers INTERSECT SELECT email FROM premium_members; -- EXCEPT在 A 中但不在 B 中的邮箱 SELECT email FROM all_users EXCEPT SELECT email FROM unsubscribed_users;四个运算符的语义分别是UNION并集去重、UNION ALL并集不去重、INTERSECT交集、EXCEPT差集。它们要求两侧结果集的列数相同且对应列类型兼容。当业务目标是是否存在/是否相同这类判断时应优先考虑 EXISTS 而非集合运算后者需要完整物化两侧结果。性能速查五条核心原则query-patterns.md 末尾的 Performance Tips 是全文精华结合仓库中 optimization.md 可以归纳为五条可执行的检查清单CTE 物化控制PostgreSQL 12 默认物化 CTE用WITH cte AS MATERIALIZED或NOT MATERIALIZED显式控制避免不必要的临时结果集写入。这在 CTE 只被引用一次或内联更优时尤其重要。JOIN 顺序现代优化器如 PostgreSQL 的基于代价优化器会自行选择连接顺序手动优化时把较小的表放在前面通常更利于嵌套循环连接但最终应以 EXPLAIN 输出为准不要盲目相信经验法则。EXISTS vs IN相关检查用EXISTS短路语义、天然规避 NULL 陷阱小且静态的列表用IN更直观。子查询 vs JOIN优先 JOIN——优化器对 JOIN 的可重写空间更大可读性也更好。这与上文子查询优化一节完全对应。UNION ALL vs UNION重复行可接受时一律用UNION ALL省去去重排序成本。同样SQL Pro 的 MUST NOT DO 还包括生产查询禁用 SELECT *、能用集合操作就不用游标这些约束在 SKILL.md 中有完整罗列。如果要系统验证上述查询是否走索引optimization.md 建议用EXPLAIN (ANALYZE, BUFFERS, VERBOSE)检查 Seq Scan 数量、预估行数与实际行数偏差、Buffer 命中情况SQL Pro 的 SKILL.md 还给出了一个明确的验收标准——EXPLAIN ANALYZE确认大表无全表扫描若查询未达到亚百毫秒目标需迭代索引选择或查询重写。总结如何把查询模式变成日常习惯query-patterns.md 覆盖了从基础 CTE 到递归层级遍历、从反连接到子查询重写、从行列转换到集合运算的完整模式图谱。建议的落地路径是遇到复杂聚合先问自己能不能用 CTE 拆解把过滤提前、聚合复用遇到每行取 Top N先想LATERAL或窗口函数而不是标量子查询涉及存在性判断统一走EXISTS/NOT EXISTS绕开 NULL 陷阱与去重开销每次改写后都用EXPLAIN ANALYZE前后对比结合 optimization.md 的监控查询定位慢 SQL跨数据库迁移前查阅 dialect-differences.md 的四库对照自增主键、字符串拼接、日期函数、LIMIT/OFFSET、UPSERT、数据类型映射等。这些模式与配套的窗口函数、索引设计和方言对照参考共同构成了 sql-pro 技能的完整知识体系既可作为 Claude Code 的上下文输入也可作为开发者日常排查 SQL 性能问题的手册。【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考