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 是否等于明细数量之和,并确认其他客户的订单不会被返回。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

编写核心业务查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通