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（结合执行计划与索引优化）→ Verify（EXPLAIN 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 Expressions（CTE）基础模式

CTE（WITH 子句）是提升复杂查询可读性与可复用性的第一工具。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;

注意两个关键细节：

• 先过滤再进入 CTE：WHERE is_active = true与WHERE status = 'completed'都在 CTE 内部完成，缩小了后续 JOIN 的输入规模。这与 sql-pro SKILL.md 的 MUST DO 约束"Apply filtering early in query execution（尽早应用过滤条件）"完全一致。
• 显式处理 NULL：LEFT 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)是防止除零的关键手段：上月没有销售记录时除数为 NULL，growth_pct结果为 NULL 而非报错。LEFT JOIN配合current.month = previous.month + INTERVAL '1 month'实现了标准的同比环比取值，比逐行子查询高效得多。

实战提示：query-patterns.md 的 Performance Tips 第 1 条提醒——PostgreSQL 12+ 默认会物化（materialize）CTE，即把 CTE 结果落成临时结果集。如果你的 CTE 只需执行一次或希望内联展开，可显式使用WITH cte AS NOT MATERIALIZED；反之若想强制缓存多次引用的计算结果，则用WITH cte AS MATERIALIZED。写复杂查询时，物化行为会影响最终执行计划，务必通过 EXPLAIN 验证。

Recursive CTE：处理树形与图结构数据

当数据呈层级关系（组织架构、BOM 物料清单、分类树）时，递归 CTE 是最简洁的解法。其语法由两部分构成：

1. Anchor member（锚点成员）：非递归的初始结果集，通常是顶层节点；
2. 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 连接：每行计算相关子查询（PostgreSQL）

SELECT 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-Join）：A 中存在但 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"的原因。

子查询优化：从 N+1 到集合化

标量子查询的 N+1 陷阱

在 SELECT 子句中使用相关标量子查询，会为外层每一行执行一次子查询，即 N+1 问题：

-- 尽量避免：每个产品行都要单独跑两次子查询 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 JOIN

SELECT 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函数接受两个文本参数：第一个是产生 (行键, 列键, 值) 三元组的查询，第二个是列键的去重有序列表。输出列的个数与类型必须与列键查询的返回行一一对应，写错会导致运行时报错。

手动 PIVOT：CASE + 聚合

不依赖扩展、各数据库通用的做法是用条件聚合：

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 可以归纳为五条可执行的检查清单：

1. CTE 物化控制：PostgreSQL 12+ 默认物化 CTE，用WITH cte AS MATERIALIZED或NOT MATERIALIZED显式控制，避免不必要的临时结果集写入。这在 CTE 只被引用一次或内联更优时尤其重要。
2. JOIN 顺序：现代优化器（如 PostgreSQL 的基于代价优化器）会自行选择连接顺序，手动优化时把较小的表放在前面通常更利于嵌套循环连接；但最终应以 EXPLAIN 输出为准，不要盲目相信经验法则。
3. EXISTS vs IN：相关检查用EXISTS（短路语义、天然规避 NULL 陷阱）；小且静态的列表用IN更直观。
4. 子查询 vs JOIN：优先 JOIN——优化器对 JOIN 的可重写空间更大，可读性也更好。这与上文子查询优化一节完全对应。
5. 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 到递归层级遍历、从反连接到子查询重写、从行列转换到集合运算的完整模式图谱。建议的落地路径是：

1. 遇到复杂聚合先问自己"能不能用 CTE 拆解"，把过滤提前、聚合复用；
2. 遇到"每行取 Top N"先想LATERAL或窗口函数，而不是标量子查询；
3. 涉及"存在性判断"统一走EXISTS/NOT EXISTS，绕开 NULL 陷阱与去重开销；
4. 每次改写后都用EXPLAIN ANALYZE前后对比（结合 optimization.md 的监控查询定位慢 SQL）；
5. 跨数据库迁移前查阅 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