PostgreSQL / 系统课时
编写核心业务查询
编写核心业务查询
本课围绕订单系统实现一个覆盖明细、多表关联、聚合和分页的查询。完成后,你将能从核心表中返回客户订单列表,并同时得到订单金额与商品数量。
学习目标
- 使用多表关联返回完整业务信息
- 使用聚合函数统计订单内商品数量
- 设计稳定、可继续优化的游标分页条件
明确查询目标
接口需要按客户查询订单,按创建时间倒序返回,每行包含订单号、状态、总金额、商品总件数和创建时间。分页不能只依赖时间,因为多条订单可能在同一时刻创建。
因此排序条件使用 created_at DESC, id DESC。下一页携带上一页最后一条记录的时间和主键,形成稳定游标。
编写分页查询
下面是片段示例,依赖前课已创建并写入数据的四张表。首次查询时,将两个游标参数传为 NULL;后续查询传入上一页最后一条记录的值。
sql
SELECT
o.id,
o.order_no,
o.status,
o.total_amount,
SUM(oi.quantity) AS total_quantity,
o.created_at
FROM orders AS o
JOIN order_item AS oi ON oi.order_id = o.id
WHERE o.customer_id = :customer_id
AND (
:cursor_time IS NULL
OR (o.created_at, o.id) < (:cursor_time, :cursor_id)
)
GROUP BY
o.id,
o.order_no,
o.status,
o.total_amount,
o.created_at
ORDER BY
o.created_at DESC,
o.id DESC
LIMIT :page_size;预期结果
text
id | order_no | status | total_amount | total_quantity | created_at
12 | ORD20260730012 | PAID | 299.00 | 3 | <TIME>
9 | ORD20260730009 | PAID | 128.00 | 1 | <TIME><TIME> 和具体 ID 会随数据变化。真实接口应使用参数绑定,不要拼接客户编号、游标或分页大小。
为什么使用游标分页
OFFSET 在页数很大时需要跳过越来越多的记录,而且数据新增后容易出现重复或遗漏。游标分页直接从上一页末尾继续,性能和结果稳定性更好。
游标字段必须与排序字段完全一致。这里只传时间而不传主键,会在相同时间戳的订单之间产生不确定顺序。
处理聚合与明细
SUM(oi.quantity) 统计订单总件数,因此查询需要按订单字段分组。若接口还要返回每个商品明细,应拆成“订单列表”和“订单详情”两个查询,避免一行订单被展开为多行后影响分页。
分页查询的粒度必须与返回对象一致。列表按订单分页,详情再按订单主键查询明细,是更清晰的职责划分。
验证查询正确性
至少验证三个边界:客户没有订单时返回空集;同一时间创建多条订单时顺序稳定;翻到下一页时不重复上一页最后一条记录。
还应核对 total_quantity 是否等于明细数量之和,并确认其他客户的订单不会被返回。
按自己的节奏,确认真正理解。