使用单个 MySQL query 生成 billing statement

2026-10-24支付老李

结论

可以用一条 UNION ALL 查询一次返回：

符合条件的 service；
与这些 service 关联的 payment；
未关联 service 的 advance payment。

排序时，不能只看 transaction 自身的日期，还需要为每条记录生成一个分组日期：

service 使用自己的 service date；
关联 payment 使用所属 service 的 service date；
advance payment 使用自己的 payment date。

这样，每条 service 后面会紧跟与它关联的 payments，而同一 service 下的 payments 再按 payment date 排序。

MySQL 8.0 及以上写法

假设查询按账户和 service 日期范围筛选：

WITH filtered_services AS (
    SELECT
        s.id,
        s.account_no,
        s.`date`,
        s.service_amount,
        s.tran_type
    FROM service AS s
    WHERE s.account_no = :account_no
      AND s.`date` >= :start_date
      AND s.`date` <  :end_date
),
transactions AS (
    /* Service 本身 */
    SELECT
        fs.account_no,
        fs.id AS transaction_id,
        fs.`date` AS transaction_date,
        fs.service_amount AS amount,
        fs.tran_type,
        fs.`date` AS group_date,
        fs.id AS group_id,
        0 AS item_order
    FROM filtered_services AS fs

    UNION ALL

    /* 与筛选结果中的 Service 关联的 Payment */
    SELECT
        p.account_no,
        p.id AS transaction_id,
        p.`date` AS transaction_date,
        p.pmt_amount AS amount,
        p.tran_type,
        fs.`date` AS group_date,
        fs.id AS group_id,
        1 AS item_order
    FROM filtered_services AS fs
    INNER JOIN payment AS p
        ON p.service_id = fs.id

    UNION ALL

    /* 未关联 Service 的 Advance Payment */
    SELECT
        p.account_no,
        p.id AS transaction_id,
        p.`date` AS transaction_date,
        p.pmt_amount AS amount,
        p.tran_type,
        p.`date` AS group_date,
        p.id AS group_id,
        0 AS item_order
    FROM payment AS p
    WHERE p.service_id IS NULL
      AND p.account_no = :account_no
)
SELECT
    account_no,
    transaction_id,
    transaction_date,
    amount,
    tran_type
FROM transactions
ORDER BY
    group_date,
    group_id,
    item_order,
    transaction_date,
    transaction_id;

各排序字段的作用如下：

group_date 决定整个 service/payment 分组在结果中的位置；
group_id 用于稳定排列同一天的多个分组；
item_order = 0 表示分组的主记录；
item_order = 1 表示与 service 关联的 payment；
transaction_date 用于排列同一 service 下的 payments；
最后的 transaction_id 用于保证同一天、同一金额等情况下的排序稳定。

为什么不能只使用 `ORDER BY date`

如果直接合并两张表，再按日期排序：

ORDER BY transaction_date

payment 会根据自己的 payment date 排入整个结果集。例如，一笔属于 11 月 20 日 service 的 payment 发生在 12 月 30 日，它就会排在其他 12 月 service 后面，无法紧跟所属的 service。

因此，关联 payment 需要同时保留两个日期：

group_date       = 所属 service 的日期
transaction_date = payment 自身的日期

查询先按 group_date 排列 service 分组，再按 transaction_date 排列组内 payment，才能得到需要的结构。

根据示例数据得到的核心顺序

查询中，关联 payment 的排序结果相当于：

SVC  2015-11-12  10
PMT  2015-11-15   5
PMT  2015-11-15   5

SVC  2015-11-20  20
PMT  2015-11-25  40
PMT  2015-12-30 -15

SVC  2015-12-13  40
PMT  2015-12-28  35

虽然 2015-12-30 晚于下一条 service 的日期，这笔 payment 仍紧跟 2015-11-20 的 service，因为它属于该 service 分组。这不是单纯按时间排列的流水账，而是按 service 归组的 billing statement。

Advance payment 的筛选范围

上面的查询假设报表只针对一个账户，因此 advance payment 使用以下条件：

WHERE p.service_id IS NULL
  AND p.account_no = :account_no

如果 service 的筛选结果可能包含多个账户，而查询只需要返回这些账户的 advance payments，可以先提取相关账户：

WITH filtered_services AS (
    SELECT
        s.id,
        s.account_no,
        s.`date`,
        s.service_amount,
        s.tran_type
    FROM service AS s
    WHERE /* service 筛选条件 */
),
filtered_accounts AS (
    SELECT DISTINCT account_no
    FROM filtered_services
)

然后把 advance payment 分支改为：

SELECT
    p.account_no,
    p.id AS transaction_id,
    p.`date` AS transaction_date,
    p.pmt_amount AS amount,
    p.tran_type,
    p.`date` AS group_date,
    p.id AS group_id,
    0 AS item_order
FROM payment AS p
INNER JOIN filtered_accounts AS fa
    ON fa.account_no = p.account_no
WHERE p.service_id IS NULL

如果 statement 需要按账户分别排序，应将 account_no 放在最终排序的第一位：

ORDER BY
    account_no,
    group_date,
    group_id,
    item_order,
    transaction_date,
    transaction_id;

否则，不同账户在相同日期的记录可能会交错出现。

使用 `UNION ALL`，不要使用 `UNION`

这里应使用：

UNION ALL

UNION 会对结果去重，通常需要额外的排序或临时表处理。账单中的两笔 payment 即使账户、日期和金额都相同，也可能是两条合法交易，不能因为字段相同而被删除。

示例中虽然包含 id，通常不会发生误去重，但 UNION ALL 更符合交易明细查询的语义，通常也更高效。

索引建议

为了避免逐条查询 service 造成 N+1 问题，至少可以考虑以下索引：

CREATE INDEX idx_service_account_date
    ON service (account_no, `date`);

CREATE INDEX idx_payment_service_date
    ON payment (service_id, `date`);

CREATE INDEX idx_payment_account_service_date
    ON payment (account_no, service_id, `date`);

是否需要保留全部索引，要根据实际筛选条件、数据量和 EXPLAIN 结果判断。索引过多也会增加插入和更新的成本。

date 字段应使用 MySQL 的 DATE、DATETIME 或 TIMESTAMP 类型，不要将日期保存成 11/12/2015 这样的字符串。查询参数建议使用无歧义的 ISO 格式：

2015-11-12

这样，日期比较和排序才会按真实时间执行，而不是按字符串顺序执行。