⚡

SQL模式速查手册Skill sql-patterns

本技能提供SQL常用模式、公共表表达式(CTE)、窗口函数、JOIN类型、分页技巧、索引策略及反模式的快速参考指南。适用于数据库查询优化、数据分析、后端开发，帮助开发者编写高效SQL语句。关键词：SQL模式，CTE示例，窗口函数，SQL连接，索引策略，分页SQL，数据库优化，查询性能。

数据工程 4 次安装 14 次浏览 更新于 2/28/2026

name: sql-patterns description: “SQL常用模式、CTE、窗口函数和索引策略的快速参考。触发词：sql模式、cte示例、窗口函数、sql连接、索引策略、分页sql。” allowed-tools: “Read Write”

SQL 模式

常用 SQL 模式的快速参考。

CTE（公共表表达式）

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

链式 CTE

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    )
SELECT u.name, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

窗口函数（快速参考）

函数 用途
ROW_NUMBER() 唯一顺序编号
RANK() 带间隔的排名 (1, 2, 2, 4)
DENSE_RANK() 无间隔的排名 (1, 2, 2, 3)
LAG(col, n) 前一行值
LEAD(col, n) 下一行值
SUM() OVER 累计总和
AVG() OVER 移动平均 
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

JOIN 参考

类型 返回结果
INNER JOIN 仅匹配的行
LEFT JOIN 所有左表行 + 匹配的右表行
RIGHT JOIN 所有右表行 + 匹配的左表行
FULL JOIN 所有行，无匹配处为 NULL

分页

-- OFFSET/LIMIT（简单，大偏移量时慢）
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- 键集分页（快速，可扩展）
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;

索引快速参考

索引类型 最佳适用场景
B-tree 范围查询，ORDER BY
Hash 仅精确相等查询
GIN 数组，JSONB，全文搜索
覆盖索引 避免表查找

反模式

错误做法 修正方法
SELECT * 显式列出列名
WHERE YEAR(date) = 2024 WHERE date >= '2024-01-01'
NOT IN 与 NULL 值 使用 NOT EXISTS
N+1 查询 使用 JOIN 或批量查询

额外资源

如需详细模式，请加载：

  • ./references/window-functions.md - 完整的窗口函数模式
  • ./references/indexing-strategies.md - 索引类型、覆盖索引、优化