主题
SQL 基础：先确定一行代表什么，再写查询
订单报表最难排查的错误，往往没有语法报错：销售额多算了一倍，没有下单的客户消失了，排行榜每次的第一名不一样。SQL 能执行，只说明数据库懂了语句，不能说明语句表达了正确的问题。
本文用“客户与订单”这组数据讲清建表、写入、连接、子查询、聚合和窗口函数，再回到表应该怎么拆。读完应能解释查询为什么产生这些行，知道哪些规则交给约束，哪些计算放在查询里。示例以 PostgreSQL 18 为方言；金额用整数分表示，避免把二进制浮点误差混进本篇的问题。完整 SQL 在文末。
一张表先回答两个问题
第一，这一行是什么？customers 一行是一名客户，orders 一行是一张订单。第二，什么能唯一认出这一行？本例使用 id，并用主键强制唯一且非空。
| 动作 | 常用语句 | 实际改变什么 | 先确认什么 |
|---|---|---|---|
| 定义或改变结构，通常称 DDL | CREATE、ALTER、DROP | 表、列、约束、索引等对象 | 是否影响旧程序，是否等待锁 |
| 写数据，通常称 DML | INSERT、UPDATE、DELETE | 具体记录 | 条件是否准确，影响多少行 |
| 读数据 | SELECT | 返回结果集 | 一行结果代表什么，重复是否合理 |
| 控制事务 | BEGIN、COMMIT、ROLLBACK | 多条操作的提交边界 | 中途失败能否整体撤销 |
有人把 SELECT 单列为 DQL，也有人归入广义 DML；分类名不影响执行语义。更重要的是别把方言差异抹掉：PostgreSQL 的 UPDATE ... RETURNING 可以返回更新后的行；MySQL 8.4 不能照搬这个写法。DDL 是否隐式提交，也必须查对应引擎，不能因为都叫 SQL 就按同一套事务规则理解。[1][2]
sql
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
amount integer NOT NULL CHECK (amount >= 0),
status text NOT NULL,
coupon text
);这些约束各管一件事。主键拒绝重复订单号；外键要求客户存在；NOT NULL 要求确实给了值；CHECK 拒绝负金额。应用层先查再插不能替代唯一约束，因为两个并发请求可能同时查到“不存在”。约束由数据库在实际写入时检查。CHECK 的表达式结果为 NULL 时并不等于失败，所以“金额不能为空”仍要单独写 NOT NULL。[3]
这也说明 NULL 不能随便用零代替。优惠券未知、没有优惠券、空字符串，是三种业务含义；本例约定 NULL 表示没有优惠券，业务若要区分“还没核实”，需要另设状态。
SQL 默认保留重复，NULL 又引入第三种判断结果
关系模型把关系理解为集合；日常 SQL 查询通常按多重集处理：两行投影出的值相同，仍可能保留两行。SELECT DISTINCT 才要求去重，UNION 去重，UNION ALL 保留重复。不要看见重复就补 DISTINCT，它可能掩盖连接条件错误，还可能引入排序或散列工作。[4][5]
普通比较运算遇到 NULL 通常得到 unknown，可用 SQL 的 NULL 表示。WHERE 只留下条件为 true 的行，false 和 unknown 都被排除。[6]
sql
SELECT NULL = NULL, NULL IS NULL, 1 NOT IN (2, NULL);本篇 PostgreSQL 实验的输出，三列依次为：
text
NULL|t|NULL第一列没有说“两边相等”，因为缺失值不提供比较依据。判断缺失应写 IS NULL。第三列最容易影响业务：1 确实不等于 2，但是否不等于一个未知值，无法确定，整个 NOT IN 因此不是 true。寻找“没有订单的客户”时，用与外层客户相关联的 NOT EXISTS，通常比赌子查询结果永远不含 NULL 更直接。注意它和 NOT IN 的差异来自语义，不能不看需求就机械互换。[7]
连接先决定行数，再谈取哪些列
一名客户可以有多张订单。把客户和订单连接，会得到“客户与订单配对”的行，客户字段会重复出现。再连接一张一对多的付款记录表，配对数还可能相乘。这是总额被放大的常见来源。
正在绘制图示…
查看图源代码
flowchart LR
C["客户 Ada：1 行"] --> J["按 customer_id 连接"]
O["Ada 的订单：3 行"] --> J
J --> R["3 行配对结果<br/>客户字段重复，订单各出现一次"]
R --> G["按客户聚合"]
G --> S["Ada：1 行汇总"]
| 连接 | 未匹配的行怎么办 | 典型问题 |
|---|---|---|
INNER JOIN | 两边不匹配的行都不出现在结果里 | 有订单的客户及订单详情 |
LEFT JOIN | 保留左边的行，缺失的右边列补 NULL | 所有客户，包括零订单客户 |
RIGHT JOIN | 与交换左右的左连接等价 | 同上，通常改写成左连接更好读 |
FULL JOIN | 两边未匹配行都保留 | 两份清单对账；MySQL 8.4 无原生 FULL OUTER JOIN |
CROSS JOIN | 每个左行与每个右行配对 | 日期 × 门店，先造完整报表网格 |
| 自连接 | 同一表以不同别名参加连接 | 员工与主管、同表记录比较 |
JOIN ... ON 写配对规则，WHERE 写对配对结果的过滤。优化器可以改写执行方式，表中的描述是结果语义，不是要求逐一做笛卡尔积。[8][9]
本例 Ada 有 3 张订单，Bo 有 1 张，Cy 没有订单：
sql
SELECT c.name, COUNT(*), COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;text
Ada|3|3
Bo|1|1
Cy|1|0Cy 仍然产生一行“右侧为空”的配对，所以 COUNT(*) 是 1。COUNT(o.id) 只计非空的订单号，才得到 0。COUNT 的这两种用法不能互换。[10]
如果目标是“所有客户的已支付订单数”，状态条件应放进 ON：
sql
LEFT JOIN orders o
ON o.customer_id = c.id AND o.status = 'paid'完整查询得到 Ada|2、Bo|1、Cy|0。把 o.status = 'paid' 移到 WHERE，结果只剩 Ada|2、Bo|1。Cy 那行的状态是 NULL，过滤阶段便把它去掉了。两种查询都合法，回答的问题不同。
当订单同时连接订单明细和支付流水时，先分别把两张子表聚合到“每张订单一行”，再连接，通常更容易保持正确的计数范围。是否还要追求更快，应在正确语义上看执行计划。
子查询：把“存在、集合、单值、临时结果”分开想
子查询是嵌在另一条查询里的查询，不是一种固定的执行算法。相关子查询引用外层列，但不意味着数据库必然为每一行完整执行一次；优化器可能转为连接等访问方式，仍要看实际计划。[7:1][11]
| 需要什么 | 写法 | 必须记住的边界 |
|---|---|---|
| 是否存在匹配行 | EXISTS (SELECT 1 ...) | 只关心是否有行，不关心返回列是什么 |
| 是否属于一组值 | IN (SELECT ...) | NULL 影响三值逻辑；特别留意 NOT IN |
| 单个计算结果 | amount > (SELECT AVG(amount) ...) | PostgreSQL 标量子查询多于一行报错；零行得到 NULL |
| 先组成一份中间结果再查询 | FROM (...) AS x 或 WITH x AS (...) | CTE 有利于表达步骤，但不保证总会物化，也不是天然更快 |
“没有订单”可以直译成“不存在归属于这个客户的订单”：
sql
SELECT c.name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.id;输出只有 Cy。这里选择 EXISTS 是因为需要存在性，不需要把订单连接出来再删除重复客户。PostgreSQL 标量子查询、CTE 是否折叠等更细的条件分别由表达式和 WITH 文档定义。[12][13]
聚合把多行收成一行，窗口保留每一行
这两种计算都可能求和，差别在结果的粒度。
sql
SELECT customer_id, COUNT(*), SUM(amount), AVG(amount)::numeric(10,2)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 150
ORDER BY customer_id;text
1|2|200|100.00WHERE 先排除未支付订单，GROUP BY 按客户收拢，HAVING 再筛掉总额不足 150 分的组。Ada 的两张已支付订单合为一行，订单号不再有唯一的答案，因此不能随手把 id 放进 SELECT。某些引擎或模式允许“裸列”，但不能据此认为它总能代表组内想要的那行。[14][15]
大部分常见聚合忽略 NULL 输入，COUNT(*) 是计行数；无输入时 COUNT 为 0，SUM 等通常为 NULL。报表要显示零，可明确写 COALESCE(SUM(amount), 0)，并确认业务确实把“没有记录”当作零。[10:1]
窗口函数则在每一行旁边附加计算结果。下面只看 Ada 的三张订单：
sql
SELECT id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS rn,
RANK() OVER (ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense,
SUM(amount) OVER (
ORDER BY amount DESC, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running
FROM orders WHERE customer_id = 1
ORDER BY amount DESC, id;text
11|100|1|1|1|100
12|100|2|1|1|200
13|50|3|3|2|250三个编号解决三个不同问题：ROW_NUMBER 不留并列，需要唯一排序条件保证选择稳定；RANK 并列占名次，下一位从 3 开始；DENSE_RANK 并列后接着排 2。按客户分别计算，只要在 OVER 中加 PARTITION BY customer_id，文末完整实验使用的就是这个版本。[16]
窗口里的 ORDER BY 决定计算次序，最外层 ORDER BY 决定结果展示次序，前者不能代替后者。累计求和还涉及 frame，也就是当前行参与计算的范围。上面显式用 ROWS，按具体行逐步累加。如果只写 SUM(amount) OVER (ORDER BY amount DESC)，默认范围包含同序值的同行，两张 100 分订单的累计结果都会是 200，第三行是 250。这是本次实验的真实结果，不是求和出错。[17][18]
要“每名客户金额最高的一张订单”，先在 CTE 中算 ROW_NUMBER，再在外层筛 rn = 1。不能在同层 WHERE 中直接筛窗口计算的结果，因为那一层还没完成窗口计算。
正在绘制图示…
查看图源代码
flowchart LR
F["FROM / JOIN<br/>确定输入行"] --> W["WHERE<br/>筛输入行"]
W --> G["GROUP BY / 聚合"]
G --> H["HAVING<br/>筛分组"]
H --> V["窗口计算<br/>保留当前粒度"]
V --> P["输出列 / DISTINCT"]
P --> O["ORDER BY / LIMIT"]
这是理解结果的逻辑顺序，不是物理执行计划。数据库会在语义等价的前提下重排、下推和选择算法。理解这张图的用途，是避免把分组后的值拿到分组前过滤，而不是据它推断 CPU 执行顺序。
范式先解决重复事实，反范式要说明谁维护副本
假设一张表同时保存 order_id、product_id、product_name、customer_id、customer_name 和数量。商品改名要改很多订单，少改一行就出现两个“当前商品名”；没有订单时还没地方存商品；删掉最后一张订单可能连商品信息也一起丢掉。这些是更新、插入、删除异常。
范式用函数依赖分析这类问题。A → B 表示在业务约束下，确定 A 就能唯一确定 B。候选键是能够唯一决定整行、且去掉任何一部分便不再足够的属性组合；主键是选中的一个候选键。主属性属于至少一个候选键，非主属性不属于任何候选键；超键能决定整行，但允许包含多余属性。范式分析针对所有候选键，不能只盯着新加的自增主键。[19]
| 范式 | 要排除的问题 | 订单例子中的改法 |
|---|---|---|
| 1NF | 在一个应按单值处理的位置塞进重复组或多个值 | 把逗号分隔的商品清单拆成订单明细行；“原子”需结合采用的数据模型理解 |
| 2NF | 非主属性只依赖某个候选键的一部分 | 明细键是 (order_id, product_id) 时，当前商品名只依赖 product_id，移到商品表 |
| 3NF | 非主属性经由另一非键属性间接依赖键 | 订单中的 customer_id → 当前客户名，客户名放客户表 |
| BCNF | 每个非平凡函数依赖的决定因素都应是超键 | 比 3NF 更严格；多候选键重叠时需专门检查，拆表还要考虑是否保留依赖 |
前三项是入门的实用解释；3NF 的精确定义允许依赖右边是主属性，因此不能把“任何传递依赖都禁止”当作完整定义。分解还需要无损连接，不能为了少重复把表拆到无法还原原关系。[19:1][20]
不过，订单里的“成交时商品名”不等于商品表里的“当前商品名”。前者是历史事实，改商品名不应该改旧订单。它与当前名称并不保持相等，也就不能简单判为需要去掉的冗余。先定义事实的时间含义，再做范式判断。
反范式则是有意保留可推导数据，例如客户表缓存“累计已支付金额”，让高频页面少做一次大聚合。读取变快，写入路径却多了一项责任：支付、退款、撤销都要更新累计值；异步维护还要接受延迟、重复消息和补偿校验。[21]
| 可以考虑保留的冗余 | 好处 | 付出的代价 |
|---|---|---|
| 订单成交快照 | 保留历史语义，减少对现状表的依赖 | 要明确哪些字段冻结、何时生成 |
| 同事务维护的汇总列 | 读取简单，提交后可保持约定的一致性 | 写放大、热点行竞争、更新路径易遗漏 |
| 异步报表表或物化结果 | 让复杂分析不干扰在线查询 | 数据延迟、回填、重建与对账流程 |
选择反范式前，写下一条可检查的等式，例如“汇总金额 = 已支付订单金额之和 − 已退款金额”，再决定更新者、事务边界和修复办法。没有维护规则的缓存字段，很快会变成两份互相矛盾的事实。
用什么办法确认自己写对了
先准备能触发错误的小数据：零订单客户、两张同金额订单、NULL、多重一对多关系，再写期望结果。比较结果行数和金额，比只看查询“有没有返回数据”可靠。写入语句先在测试库中核对 WHERE 和影响行数，必要时置于事务里检查后回滚。
本次验证采用 PostgreSQL 18.6，Linux/amd64，从官方源码及 SHA-256 校验文件构建，只在隔离临时数据目录中使用 single-user 离线模式执行。正文输出省略了该模式的 backend> 提示符；查询结果由 COPY ... TO STDOUT 输出。basics.sql 的查询、更新、删除和窗口结果全部实际运行；负金额反例实际触发约束错误，文末验证脚本将 SQL 错误转换为退出码 1。没有在 MySQL 实测本篇查询，方言区别来自其 8.4 官方文档。小数据验证的是语义，不是执行性能。single-user 模式没有真实的进程间通信与并发锁行为，不能用于验证并发隔离；本篇也没有验证生产规模或故障恢复。[22]
参考资料
完整复现材料
需要 PostgreSQL 18.6 的 postgres、initdb 和 Python 3。只使用新建、可丢弃、没有其他服务进程占用的数据目录；不要对已有数据库执行以下命令。POSTGRES 可以设为该版本可执行文件的绝对路径。普通 PostgreSQL 客户端也能执行两份 SQL，但本次实测使用下列离线路径，没有运行 psql：
sh
export POSTGRES=/path/to/postgresql-18.6/bin/postgres
export PGDATA="$(mktemp -d)/data"
/path/to/postgresql-18.6/bin/initdb -D "$PGDATA" -A trust --no-locale -E UTF8
python3 run_single.py basics.sql
python3 run_single.py constraint_failure.sql
# 第二条故意触发 CHECK 错误，验证脚本退出码为 1。run_single.py 仅服务本文受控测试文件，按分号拆分语句，不适合带分号字符串、函数体等任意 SQL。它忽略客户端排版指令，把 SELECT 包成 COPY 以显示结果，发现服务端 ERROR 则报告失败。下面是全部输入及脚本。
basics.sql
代码块 · 61 行 · 点击收起 / 展开
sql
\set ON_ERROR_STOP on
\pset format unaligned
\pset tuples_only on
\pset null 'NULL'
BEGIN;
CREATE TEMP TABLE customers(id integer PRIMARY KEY, name text NOT NULL);
CREATE TEMP TABLE orders(
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
amount integer NOT NULL CHECK(amount >= 0),
status text NOT NULL,
coupon text
);
INSERT INTO customers VALUES(1,'Ada'),(2,'Bo'),(3,'Cy');
INSERT INTO orders VALUES
(11,1,100,'paid',NULL),(12,1,100,'paid','WELCOME'),
(13,1,50,'pending',NULL),(21,2,80,'paid',NULL);
\echo join-count
SELECT c.name, COUNT(*), COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;
\echo left-on
SELECT c.name,COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid'
GROUP BY c.id,c.name ORDER BY c.id;
\echo left-where
SELECT c.name,COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id=c.id
WHERE o.status='paid' GROUP BY c.id,c.name ORDER BY c.id;
\echo nulls
SELECT NULL = NULL, NULL IS NULL, 1 NOT IN(2,NULL);
\echo anti-join
SELECT c.name FROM customers c WHERE NOT EXISTS
(SELECT 1 FROM orders o WHERE o.customer_id=c.id) ORDER BY c.id;
\echo aggregate
SELECT customer_id, COUNT(*),SUM(amount),AVG(amount)::numeric(10,2)
FROM orders WHERE status='paid'
GROUP BY customer_id HAVING SUM(amount)>=150 ORDER BY customer_id;
\echo windows
SELECT id,amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC,id) AS rn,
RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS dense,
SUM(amount) OVER(PARTITION BY customer_id ORDER BY amount DESC,id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders WHERE customer_id=1 ORDER BY amount DESC,id;
\echo default-frame
SELECT id,amount,SUM(amount) OVER(ORDER BY amount DESC)
FROM orders WHERE customer_id=1 ORDER BY amount DESC,id;
\echo top-one
WITH ranked AS (
SELECT id,customer_id,amount,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC,id) AS rn
FROM orders
) SELECT customer_id,id,amount FROM ranked WHERE rn=1 ORDER BY customer_id;
\echo scalar
SELECT id FROM orders WHERE amount>(SELECT AVG(amount) FROM orders) ORDER BY id;
\echo dml
UPDATE orders SET status='paid' WHERE id=13 RETURNING id,status;
DELETE FROM orders WHERE id=13 RETURNING id;
ROLLBACK;constraint_failure.sql
sql
\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE products(id integer PRIMARY KEY, price integer NOT NULL CHECK(price>=0));
INSERT INTO products VALUES(1,-1);
ROLLBACK;run_single.py
代码块 · 42 行 · 点击收起 / 展开
python
"""Run the deliberately simple fixtures shipped with these articles.
Input: ordinary SQL and optional psql \\set/\\pset/\\echo directives.
This is a fixture adapter, not a general SQL parser. No fixture may contain
semicolons inside quoted values, comments, function bodies, or identifiers.
"""
import os
from pathlib import Path
import re
import subprocess
import sys
sql = Path(sys.argv[1]).read_text()
lines = []
for line in sql.splitlines():
if line.startswith('\\echo '):
label = line[len('\\echo '):].replace("'", "''")
lines.append("SELECT '" + label + "';")
elif line.startswith('\\'):
continue
else:
lines.append(line)
commands = []
for part in '\n'.join(lines).split(';'):
part = part.strip()
if not part:
continue
# COPY gives legible values; it does not change the SELECT's semantics.
if re.match(r'(?i)^(SELECT|WITH)\b', part):
part = "COPY (" + part + ") TO STDOUT WITH (DELIMITER '|', NULL 'NULL')"
commands.append(part + ';\n\n')
run = subprocess.run(
[os.environ.get('POSTGRES', 'postgres'), '--single', '-j',
'-D', os.environ['PGDATA'], 'postgres'],
input=''.join(commands), text=True, capture_output=True)
print(run.stdout, end='')
print(run.stderr, end='', file=sys.stderr)
if run.returncode:
sys.exit(run.returncode)
# The stand-alone backend may recover from a SQL error and exit zero.
# An ERROR is still a failed fixture; expected failures are checked outside.
if re.search(r'\b(ERROR|FATAL|PANIC):', run.stderr):
sys.exit(1)