檢查這份材料
我們的應收金額對照金流服務商的匯出檔,逐筆訂單比較
材料: sql · 5 則註解
1WITH orders(order_id, customer_id, order_ts, status, coupon,2 shipping_fee) AS (3 VALUES4 (101, 1, '2025-02-28 21:15', 'paid', NULL, 5),5 (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0),6 (103, 2, '2025-03-09 14:30', 'paid', NULL, 5),7 (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5),8 (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5),9 (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0),10 (107, 2, '2025-03-28 12:00', 'paid', NULL, 5),11 (108, 4, '2025-03-31 18:20', 'paid', NULL, 5),12 (109, 1, '2025-04-01 08:00', 'paid', NULL, 5)13),14order_lines(line_id, order_id, product, quantity, unit_price) AS (15 VALUES16 ( 1, 101, 'Mug', 2, 8),17 ( 2, 102, 'Kettle', 1, 30),18 ( 3, 102, 'Tea tin', 2, 12),19 ( 4, 103, 'Mug', 1, 8),20 ( 5, 103, 'Tea tin', 1, 12),21 ( 6, 103, 'Filter', 3, 4),22 ( 7, 104, 'Kettle', 1, 30),23 ( 8, 105, 'Tea tin', 3, 12),24 ( 9, 106, 'Mug', 4, 8),25 (10, 106, 'Filter', 2, 4),26 (11, 107, 'Tea tin', 1, 12),27 (12, 107, 'Mug', 1, 8),28 (13, 108, 'Kettle', 1, 30),29 (14, 108, 'Filter', 1, 4),30 (15, 109, 'Tea tin', 2, 12)31),32processor_export(order_id, charged) AS (33 VALUES34 (102, 54),35 (103, 37),36 (106, 40),37 (107, 25),38 (108, 34)39),40line_totals AS (41 SELECT order_id, SUM(quantity * unit_price) AS lines_total42 FROM order_lines43 GROUP BY order_id44),45ours AS (46 SELECT o.order_id, t.lines_total + o.shipping_fee AS expected_charge47 FROM orders AS o48 JOIN line_totals AS t ON t.order_id = o.order_id49 WHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01'50 AND o.status = 'paid'51)52SELECT ours.order_id,53 ours.expected_charge,54 p.charged,55 ours.expected_charge - p.charged AS difference56FROM ours57LEFT JOIN processor_export AS p ON p.order_id = ours.order_id58ORDER BY ours.order_id;
- 第 32–39 行
金流服務商回報的 3 月實際收款,每筆訂單一列。實務上,你會從它的檔案載入,而不是自己輸入。
- 第 40–44 行
第 4 課的修正:聯結之前,先把明細縮成每筆訂單一個合計,就不會有東西被重複。
- 第 45–51 行
每筆 3 月已付款訂單:明細合計加運費。這是顧客應該被收取的金額。
- 第 55 行
兩個來源一致的地方是零。這裡如果是 NULL,就表示匯出檔裡沒有這筆訂單的收款。
- 第 57 行
LEFT JOIN 讓匯出檔裡缺少的訂單,在 charged 顯示 NULL,而不是從比較中消失。
預期輸出: order_id expected_charge charged difference
-------- --------------- ------- ----------
102 54 54 0
103 37 37 0
106 40 40 0
107 25 25 0
108 39 34 5
材料包含 58 行和 5 則註解。