年度付款记录的 Database design 与 SELECT 查询

2026-10-24支付阿杰

结论

不必每年新增一个字段。现有的“客户表 + 付款记录表”结构基本合理，可以继续让每条付款记录对应一个客户和一个年度。

查询时可使用条件聚合，将不同年份的付款状态转换为列：

SELECT
    c.customer_name,
    MAX(CASE WHEN p.year = 2017 THEN p.paid END) AS paid_for_2017,
    MAX(CASE WHEN p.year = 2018 THEN p.paid END) AS paid_for_2018,
    MAX(CASE WHEN p.year = 2019 THEN p.paid END) AS paid_for_2019
FROM customers AS c
LEFT JOIN payments AS p
    ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

这种写法通常称为条件聚合，即把按行保存的年度记录“透视”为列。

将缺少付款记录的年份显示为未支付

查询使用了 LEFT JOIN，因此客户在某个年度没有付款记录时，对应结果会是 NULL。如果业务上需要显示为 FALSE 或 0，可以使用 COALESCE：

SELECT
    c.customer_name,
    COALESCE(
        MAX(CASE WHEN p.year = 2017 THEN p.paid END),
        FALSE
    ) AS paid_for_2017,
    COALESCE(
        MAX(CASE WHEN p.year = 2018 THEN p.paid END),
        FALSE
    ) AS paid_for_2018,
    COALESCE(
        MAX(CASE WHEN p.year = 2019 THEN p.paid END),
        FALSE
    ) AS paid_for_2019
FROM customers AS c
LEFT JOIN payments AS p
    ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

使用前需要先明确各个值的业务含义：

NULL：该年度没有付款记录，状态未知。
FALSE：有明确记录，表示尚未支付。
TRUE：已经支付。

如果“没有记录”和“明确未支付”代表两种不同状态，就不应使用 COALESCE 将它们合并。

部分数据库不允许直接对布尔值使用 MAX()。遇到这种情况，可以先将状态转换为整数：

SELECT
    c.customer_name,
    COALESCE(
        MAX(CASE WHEN p.year = 2017 AND p.paid = TRUE THEN 1 ELSE 0 END),
        0
    ) AS paid_for_2017,
    COALESCE(
        MAX(CASE WHEN p.year = 2018 AND p.paid = TRUE THEN 1 ELSE 0 END),
        0
    ) AS paid_for_2018
FROM customers AS c
LEFT JOIN payments AS p
    ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

其中，1 表示已支付，0 表示未支付。

如果每个客户每年只能有一条记录

可以在数据库层添加唯一约束，防止同一个客户在同一年度出现多条付款状态：

ALTER TABLE payments
ADD CONSTRAINT uq_payments_customer_year
UNIQUE (customer_id, year);

同时应为 customer_id 建立外键：

ALTER TABLE payments
ADD CONSTRAINT fk_payments_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

采用这种设计时，MAX()不是用来比较多笔付款，而是为了完成条件聚合所需的分组。

如果一个客户每年可以有多笔付款，则要先确定年度状态的计算规则，例如：

任意一笔已支付，就将该年度视为已支付；
所有款项都已支付，才将该年度视为已支付；
根据年度应付金额和实付金额判断；
只采用最新一条记录。

上面的 MAX(CASE ...) 表示“至少有一条已支付记录，就返回已支付”。如果业务规则不同，查询也要随之调整。

是否需要修改表结构

不要将付款表改成下面这种结构：

customer_id
paid_for_2017
paid_for_2018
paid_for_2019
...

这种设计每年都要修改表结构，相关查询、索引和程序代码也会越来越复杂。按年度将付款状态保存为多行记录，更符合关系数据库的规范化设计。

现有结构可以做一些小调整：

将 year 换成含义更明确的名称，例如 payment_year 或 fee_year。YEAR 在部分数据库中可能是类型名或函数名，虽然不一定禁止使用，但容易引起歧义。
如果 payer 指实际付款人，且付款人也是系统中的客户，建议保存付款人的 ID，而不是姓名。
如果系统需要处理金额、退款、部分付款或多次付款，仅保存一个 paid 布尔值可能不够。可以增加 amount_due、amount_paid、paid_at 和 status 等数据，再根据实际记录计算年度状态。

简化后的结构可以写成：

CREATE TABLE payments (
    payment_id     BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    payment_year  INTEGER NOT NULL,
    paid           BOOLEAN NOT NULL DEFAULT FALSE,
    payer          VARCHAR(255),
    CONSTRAINT fk_payments_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id),
    CONSTRAINT uq_payments_customer_year
        UNIQUE (customer_id, payment_year)
);

年份数量不固定时

普通 SQL 查询返回多少列，必须在执行前确定。如果需要展示的年份不断变化，就无法用一条完全静态的通用查询自动生成任意数量的 paid_for_年份 列。

通常有三种处理办法：

在 SQL 中明确列出需要展示的年份；
使用数据库提供的动态 SQL 或 PIVOT 功能；
查询按行保存的数据，再由应用程序或报表工具完成透视。

保留按行返回的结果通常更容易维护：

SELECT
    c.customer_name,
    p.year,
    p.paid
FROM customers AS c
LEFT JOIN payments AS p
    ON p.customer_id = c.customer_id
ORDER BY
    c.customer_name,
    p.year;

如果页面只展示最近几年，静态条件聚合最直接。如果年份完全由数据决定，可以由应用层生成列，或者使用目标数据库支持的动态透视功能。