正确做法是用WITH将每层子查询物化为派生表以固化数据边界，存在性检查改用NOT EXISTS防NULL漏断言，浮点比对须用ABS容差且单位对齐，性能测试必须结合EXPLAIN断言执行计划。
用派生表替代子查询做逐层快照固化
多层嵌套子查询最难测的不是逻辑，而是“某一层查出来的东西，在下一层执行时是否还是它”。比如外层用 NOW()，内层又依赖用户等级快照，不同数据库可能在不同时间点取值——测试脚本跑一次结果对，再跑一次就错。
正确做法是把每一层子查询提前物化成带别名的派生表，用 WITH 显式固化中间结果：
WITH order_items_sum AS ( SELECT order_id, SUM(amount) AS sum_amount FROM order_items WHERE created_at >= '2024-06-01' GROUP BY order_id ), orders_with_level AS ( SELECT o.*, u.level_at_order_time FROM orders o LEFT JOIN users_snapshots u ON u.user_id = o.user_id AND u.snapshot_date = '2024-06-01' ) SELECT ... FROM orders_with_level o LEFT JOIN order_items_sum t ON t.order_id = o.id;
这样每层都有确定的数据边界，单元测试可针对每个 CTE 单独断言行数、空值比例、关键字段分布。
NOT EXISTS 替代 IN 防 NULL 导致的漏断言
写校验 SQL 时习惯用 IN 判断存在性，但测试一跑就发现「该报 mismatch 的没报」——根本原因是子查询里返回了 NULL，整个 WHERE x IN (subquery) 变成 UNKNOWN，该行直接被过滤，连进结果集的机会都没有。
单元测试必须覆盖 NULL 场景，所以存在性检查一律改用 NOT EXISTS：
-
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)—— 稳定返回“不存在”的订单 - 测试时可人工插入一条
customer_id IS NULL的订单，验证它是否进入异常报告 - 避免在子查询里写
SELECT *，只写SELECT 1或主键，减少执行计划抖动
浮点比对必须加 ABS 容差且单位对齐
校验明细总和与汇总字段是否一致，直接写 WHERE (SELECT SUM(amount)) = total 几乎必挂。浮点存储误差 + DECIMAL 和 FLOAT 类型隐式转换，会让 = 判定永远为 false。
单元测试中要显式模拟这种误差场景：
- 构造一笔
amount = 99.995的明细（入库后存为DECIMAL(10,2)→ 四舍五入成 100.00），再让汇总字段是原始浮点值 - 比对逻辑必须用
ABS(a - b) ，容差设为业务最小单位（分） - 若明细按
DATE(created_at)聚合，汇总表也必须用相同表达式过滤，否则索引失效导致全表扫描，测试超时
EXPLAIN + 行数断言是唯一可信的性能回归检测
相关子查询（比如 WHERE amount > (SELECT MAX(amount) FROM sales s2 WHERE s2.product_id = s1.product_id)）在数据量变大后会指数级变慢，但单靠肉眼或结果对错看不出问题。
单元测试里必须加入执行计划断言：
- 用
EXPLAIN FORMAT=JSON提取rows_examined和key字段 - 对每层子查询单独跑
EXPLAIN，确认sales.product_id有索引且被命中 - 测试用例里 assert 每个子查询的
rows_examined不超过阈值（如 1000 行），否则视为性能回归
嵌套越深，中间层数据膨胀越隐蔽；不看 EXPLAIN，只比最终结果，等于没测。
