SELECT
order_id,
user_id,
status,
total_amount,
discount_amount,
pay_amount,
created_at
FROM orders
WHERE order_id = 'ORDER_202608090001';
查询用户最近创建的订单
SELECT order_id, status, pay_amount, created_at
FROM orders
WHERE user_id = 10086
AND created_at >= '2026-08-09 10:00:00'
AND created_at < '2026-08-09 11:00:00'
ORDER BY created_at DESC
LIMIT 20;
查询条件要具体
优先使用订单号、用户 ID、幂等键等精确标识。
时间范围同时写开始和结束，避免查到其他测试数据。
明确排序和数量限制，防止结果顺序不稳定。
不要用 SELECT *
只查询本次要验证的字段。
降低误读敏感字段和大字段的风险。
字段变化时，校验脚本更容易发现影响。
04
用 JOIN 核对订单与商品明细
关联数据不能断
JOIN 使用关联键，把分散的数据重新拼成一笔完整订单
主表orders
order_id = ORDER_001
order_id
明细order_items
SKU、数量、单价
sku_id
商品products
库存与商品信息
先根据验证目的选择 JOIN
方式
保留的数据
下单测试用途
INNER JOIN
只保留两边都匹配的数据
找出订单与明细完整匹配的记录
LEFT JOIN
保留左表全部数据
发现没有商品明细的异常订单
多表 JOIN
沿业务外键继续关联
同时核对订单、商品、优惠券和用户
查询订单及其商品明细
SELECT
o.order_id,
o.status,
i.sku_id,
i.quantity,
i.unit_price,
i.quantity * i.unit_price AS line_amount
FROM orders AS o
JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.order_id = 'ORDER_202608090001';
找出没有商品明细的异常订单
SELECT o.order_id, o.status, o.created_at
FROM orders AS o
LEFT JOIN order_items AS i
ON i.order_id = o.order_id
WHERE i.order_id IS NULL
AND o.created_at >= '2026-08-09 00:00:00';
SELECT
order_id,
SUM(quantity) AS total_quantity,
SUM(quantity * unit_price) AS calculated_total
FROM order_items
WHERE order_id = 'ORDER_202608090001'
GROUP BY order_id;
检查是否产生重复订单
SELECT
idempotency_key,
COUNT(*) AS order_count
FROM orders
WHERE user_id = 10086
AND created_at >= '2026-08-09 10:00:00'
GROUP BY idempotency_key
HAVING COUNT(*) > 1;
WHERE 先过滤
WHERE 在分组前过滤原始行，例如限定本轮测试订单和时间范围。
HAVING 再筛选
HAVING 在分组后过滤汇总结果，例如只保留出现两次以上的幂等键。
06
重新计算订单金额
不要只相信结果字段
金额不是孤立字段，而是一条可以重新计算的等式
商品明细Σ 数量 × 单价
优惠金额满足门槛后扣减
实付金额原始金额 - 优惠
原始金额 - 优惠金额 = 实付金额
用商品明细重新计算并与订单主表对比
SELECT
o.order_id,
o.total_amount AS stored_total,
SUM(i.quantity * i.unit_price) AS calculated_total,
o.discount_amount,
o.pay_amount,
SUM(i.quantity * i.unit_price)
- o.discount_amount AS calculated_pay,
o.pay_amount - (
SUM(i.quantity * i.unit_price) - o.discount_amount
) AS difference
FROM orders AS o
JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.order_id = 'ORDER_202608090001'
GROUP BY o.order_id, o.total_amount,
o.discount_amount, o.pay_amount;
SELECT sku_id, stock, version, updated_at
FROM products
WHERE sku_id = 'SKU_10001';
核对本轮成功订单扣减的商品数量
SELECT
i.sku_id,
SUM(i.quantity) AS sold_quantity
FROM order_items AS i
JOIN orders AS o
ON o.order_id = i.order_id
WHERE i.sku_id = 'SKU_10001'
AND o.status IN ('PENDING_PAYMENT', 'PAID', 'COMPLETED')
AND o.created_at >= '2026-08-09 10:00:00'
AND o.created_at < '2026-08-09 11:00:00'
GROUP BY i.sku_id;
-- 请求完成后执行只读核对
SELECT sku_id, stock
FROM products
WHERE sku_id = 'SKU_LAST_ONE';
SELECT order_id, user_id, status
FROM orders
WHERE test_run_id = 'RUN_20260809_CONCURRENT'
ORDER BY created_at;
-- 预期：库存为 0，只有一个请求成功创建有效订单
SELECT
o.order_id,
o.total_amount,
COALESCE(SUM(i.quantity * i.unit_price), 0) AS item_total
FROM orders AS o
LEFT JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.created_at >= '2026-08-09 00:00:00'
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <>
COALESCE(SUM(i.quantity * i.unit_price), 0);
记录足够复现的信息
测试环境、版本、执行时间和测试账号。
订单号、商品 SKU、幂等键和本轮测试标识。
页面截图、接口请求与响应、实际 SQL 结果。
预期关系、实际差异和受影响的数据范围。
10
把 SQL 变成可重复执行的校验
查询结果要能判定
可执行校验需要明确输入、查询、判断和证据
INPUT输入
订单号与测试标识
QUERY查询
只读 SQL
ASSERT判断
0 行或明确差值
EVIDENCE证据
保存失败记录
用查询直接返回失败记录
SELECT
o.order_id,
o.pay_amount,
o.total_amount - o.discount_amount AS expected_pay
FROM orders AS o
WHERE o.test_run_id = 'RUN_20260809_001'
AND o.pay_amount <>
o.total_amount - o.discount_amount;
-- 通过标准：返回 0 行；有返回值时，每一行都是待定位差异