交付销售对账结果

介绍

PAPER & PIER 需要一份收摊交接材料：每笔订单一行的 CSV，以及每个摊位的汇总。即使两个摊位的总体差额都为零，个别订单仍可能少付或多付。交接材料必须保留这些异常，不能仅凭一个总额就宣布全部正常。

你将保存可复用的 SQL 查询，并用提供的导出工具，根据其实际结果创建真实 CSV 文件。请先完成“确保每笔订单总额正确”，或掌握 SUM/GROUP BY、LEFT JOIN、COALESCE 和连接重复问题。这里会讲解公用表表达式和状态分类。全程使用 Terminal；本单元有独立的六订单账本。

保持四个源表和提供的工具不变。只修改 query.sql 和 reports/ 下的文件；不要添加数据库对象。所有金额均为整数分。这是简化的收款对账，不涉及税费或支付服务商结算。导出工具负责 CSV 编码和汇总计算；你无需编写 Python。

保存可复用的逐订单查询

本步骤中，你将使用一条自包含的 SQL 语句，计算每笔订单的净收款、差额和状态。从 Terminal 开始：

cd /home/labex/project/sales-desk

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

这个独立账本包含 A1 到 A6，其中两笔订单的标签为 Alex Kim，还有一些标签包含逗号或引号字符。ID 用于区分订单；标签必须与存储内容完全一致。推导余额前，先查看每个事实列表：

sqlite3 -header -column ledger.db "SELECT * FROM items ORDER BY item_id; SELECT * FROM payments ORDER BY payment_id; SELECT * FROM refunds ORDER BY refund_id;"

和之前一样，只计入 succeeded 付款和退款。两笔金额相同的成功事件都要计入。缺少符合条件的事实时计为零。计算规则如下：

公用表表达式（CTE）是命名的中间 SELECT，只在一条语句内部可用。WITH name AS (...) 引入它；多个 CTE 之间用逗号分隔。它提供与之前视图相同的独立事实处理方式，但不创建数据库对象，也不依赖另一个实验的文件。

首先，三个 CTE 分别独立聚合各类事实。然后，totals 从订单开始，将缺失总额填为零。balances 根据这些命名总额计算净收款和差额。最后一个 SELECT 使用 CASE WHEN ... THEN ... ELSE ... END，根据差额的正负选择状态。通过这些层次，别名可供后面的 SELECT 使用，而不必在同一个 SELECT 列表中依赖另一个表达式的别名。中间余额层中的 SELECT * 传递之前的总额；最终 SELECT 则按要求的顺序明确列出恰好九列。

保存完整语句。cat > 替换文件；带引号的 here-document 按字面保留 SQL。包含最后一行 SQL：

cat > query.sql <<'SQL'
WITH
item_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS due_cents
  FROM items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'succeeded' GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount_cents) AS refunded_cents
  FROM refunds WHERE status = 'succeeded' GROUP BY order_id
),
totals AS (
  SELECT o.order_id, o.customer, o.stall,
         COALESCE(i.due_cents, 0) AS due_cents,
         COALESCE(p.paid_cents, 0) AS paid_cents,
         COALESCE(r.refunded_cents, 0) AS refunded_cents
  FROM orders AS o
  LEFT JOIN item_totals AS i ON i.order_id = o.order_id
  LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
  LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
),
balances AS (
  SELECT *, paid_cents - refunded_cents AS net_cents,
         paid_cents - refunded_cents - due_cents AS difference_cents
  FROM totals
)
SELECT order_id, customer, stall, due_cents, paid_cents,
       refunded_cents, net_cents, difference_cents,
       CASE WHEN difference_cents = 0 THEN 'settled'
            WHEN difference_cents < 0 THEN 'underpaid'
            ELSE 'overpaid' END AS status
FROM balances
ORDER BY order_id;
SQL

不要在 query.sql 中加入 SQLite 点命令或 shell 命令：导出工具执行的是一条普通 SELECT/WITH 语句。它必须能基于另一个相同结构账本的四个源表运行，包括不同订单 ID 和金额。硬编码结果行或依赖数据库视图都不能提供这样的可复用查询。

< 将此文件送入 SQLite CLI，进行第一次易读的结果查看：

sqlite3 -header -column ledger.db < query.sql

读取实际的每一行，而不仅是总额。预期余额如下：

A2 没有付款，仍然可见。A3 保留了两笔成功的 50 分退款。A6 失败的退款不会减少其净收款。A1 和 A2 尽管顾客标签相同，仍分别保留。五个以分为单位的列都是真正的整数，包括负差额；不要将它们转换成格式化的货币字符串。

如果有语法错误，读取报告的位置，用修正后的完整代码块替换 query.sql，再重新运行。路径错误时，使用上面的绝对路径 cd 返回，再用 ls 查找 query.sql。不要清空账本、修改金额来强行平账，或添加存储结果的表。

交付并读取实际 CSV 文件

本步骤中，你将导出已保存的查询，并读取真实的交接文件，包括需要 CSV 引号的标签。从 Terminal 开始，工作目录为 /home/labex/project/sales-desk。

./export 是当前目录中提供的可执行文件。它读取 query.sql，通过只读连接在 ledger.db 上执行原样语句，并将实际返回的行写入 reports/orders.csv。它只根据这些返回行生成 reports/summary.csv，不会修复错误查询或补入缺失订单。两个文件都应放在已准备好的 reports 目录中：

./export

消息会报告查询返回六行，并列出两个输出文件名。下面的读取工具会显示两个摊位汇总。导出成功表示文件已写出，并不表示金额计算正确。如果查询缺失或无效，导出工具会报告失败。之前成功导出的旧文件可能仍然存在：先修复保存的查询并重新运行 ./export，再将这些文件当作当前结果。

./review 是提供的 CSV 读取工具。它只读取保存的文件，不查询数据库，并将解析出的每一行显示为带标签的记录。缺失或被改动的输出会原样保持缺失或被改动的状态：

./review

六条订单记录应与上一步表格一致，顾客和摊位标签也必须与原始内容完全相同。汇总列如下：

North, Pier 有三笔订单：应收 1650、净收款 1650、差额 0，三种状态各一笔。South 也有三笔：应收 1100、净收款 1100、差额 0，三种状态各一笔。两个摊位合计有四笔未结清订单，尽管总差额为零。多付款与少付款会在总额中抵消，但不会替其他人的订单结清款项。

CSV 指以逗号分隔的值，但标签内部的逗号仍属于同一个字段。标准 CSV 使用双引号包围这种字段，并将字段内部的一个引号表示为连续两个引号。cat 把原始文件字节显示为文本；将其编码与 ./review 正确解析的标签进行比较：

cat reports/orders.csv

cat reports/summary.csv

订单文件中，顾客标签 Paper, "Pine" 编码为 "Paper, ""Pine"""；North, Pier 编码为 "North, Pier"。这些额外的引号是 CSV 语法，并不表示标签发生变化。按每个逗号拆分每一行会破坏这些记录；提供的写入和读取工具会处理这种格式。

如果缺少 CSV，先修复 query.sql，重新运行 ./export，再读取两个文件。不要手工修改汇总来隐藏缺失订单，也不要替换导出或读取工具、修改源记录。交接成果由可复用的 query.sql 和两个真实 CSV 文件组成。文件应包含每笔源订单的一行、准确的九个订单列，以及上面列出的八个汇总列。行顺序不是业务结果；标识、数值、标签及其关系才是。

总结

你构建了可复用的 CTE 查询，根据净收款减去商品金额的差额，对每笔订单进行分类，并交付了两个真实 CSV 文件。你读取了正确加引号的标签，也区分了摊位总额平衡与每笔订单均已结清这两种情况。