SQL 查询返回指定 Payment Status 并解决 invalid column name

2026-10-24支付小周

结论

Payment_Status 是在 SELECT 中定义的列别名。同一层查询的 WHERE 子句通常不能直接引用这个别名，因此会出现 invalid column name Payment_Status 错误。

要把原始状态转换成 Paid 和 Unpaid，可以使用 CASE 表达式：

CASE
    WHEN mm_payment.mm_paymentstatus = 'paid' THEN 'Paid'
    ELSE 'Unpaid'
END AS Payment_Status

如果还要筛选原始付款状态，WHERE 中应使用真实列名 mm_payment.mm_paymentstatus，不能使用别名 Payment_Status。

推荐写法

下面的查询会把 paid 显示为 Paid，把 awaiting payment 显示为 Unpaid：

SELECT
    Account.mm_registrationnumber AS CL_Reg_Number,
    Account.Name,
    Account.mm_supplierstatus AS Supplier_Status,
    Account.PrimaryContactIdName AS Primary_Contact,
    Account.EMailAddress1 AS Email,
    Account.Telephone1 AS Telephone,
    mm_address.mm_line1 AS Line1,
    mm_address.mm_line2 AS Line2,
    mm_address.mm_line3 AS Line3,
    mm_address.mm_line4 AS Line4,
    mm_address.mm_city AS City,
    mm_address.mm_county AS County,
    mm_address.mm_postcode AS Postcode,
    mm_turnover.mm_name AS Signup_Turnover,
    Account.mm_signupdate AS SignUp_Date,
    mm_payment.mm_paymenttype AS Payment_Type,
    CASE
        WHEN mm_payment.mm_paymentstatus = 'paid' THEN 'Paid'
        WHEN mm_payment.mm_paymentstatus = 'awaiting payment' THEN 'Unpaid'
        ELSE 'Unpaid'
    END AS Payment_Status,
    mm_payment.mm_vatamount AS Payment_Amount_VAT,
    mm_payment.mm_paymentamount AS Payment_Amount_Net,
    Invoice.InvoiceNumber AS Invoice_ID,
    Invoice.DueDate AS Due_Date,
    Invoice.CreatedOn AS Created_Date
FROM Account
INNER JOIN mm_payment
    ON mm_payment.mm_organisation = Account.AccountId
INNER JOIN mm_address
    ON Account.mm_address = mm_address.mm_addressId
INNER JOIN mm_turnover
    ON Account.mm_turnover = mm_turnover.mm_turnoverId
INNER JOIN Invoice
    ON Invoice.AccountId = Account.AccountId;

如果数据库中还可能出现 cancelled、refunded、NULL 等其他状态，不宜把它们全部归入 Unpaid。可以将这些状态保留为未知，以便发现数据问题：

CASE
    WHEN mm_payment.mm_paymentstatus = 'paid' THEN 'Paid'
    WHEN mm_payment.mm_paymentstatus = 'awaiting payment' THEN 'Unpaid'
    ELSE 'Unknown'
END AS Payment_Status

为什么不能在 WHERE 中使用 Payment_Status

按照 SQL 的逻辑处理顺序，WHERE 先于 SELECT 执行。处理 WHERE 时，下面定义的别名还没有生成：

mm_payment.mm_paymentstatus AS Payment_Status

所以，以下写法会报错：

WHERE Payment_Status = 'Paid'

如果只想筛选数据库中已经付款的记录，应直接使用真实列名：

WHERE mm_payment.mm_paymentstatus = 'paid'

完整示例如下：

SELECT
    Account.Name,
    mm_payment.mm_paymentstatus AS Payment_Status
FROM Account
INNER JOIN mm_payment
    ON mm_payment.mm_organisation = Account.AccountId
WHERE mm_payment.mm_paymentstatus = 'paid';

这段查询只会返回已付款记录，不会返回 Unpaid。如果需要显示所有公司，并将状态统一显示为 Paid 或 Unpaid，就不要添加这个筛选条件，只需通过 CASE 转换显示值。

只处理指定的两种原始状态

如果数据库中还有其他付款状态，但查询结果只需要包含 paid 和 awaiting payment，可以同时使用 CASE 和 WHERE IN：

SELECT
    Account.Name,
    CASE
        WHEN mm_payment.mm_paymentstatus = 'paid' THEN 'Paid'
        WHEN mm_payment.mm_paymentstatus = 'awaiting payment' THEN 'Unpaid'
    END AS Payment_Status
FROM Account
INNER JOIN mm_payment
    ON mm_payment.mm_organisation = Account.AccountId
WHERE mm_payment.mm_paymentstatus IN ('paid', 'awaiting payment');

这里，WHERE 用于筛选数据库中的原始值，CASE 用于转换查询结果中的显示内容。

必须按转换后的别名筛选时

如果需要按照计算后的 Payment_Status 筛选，可以先在 CTE 或子查询中生成这个别名，再由外层查询使用它：

WITH PaymentData AS
(
    SELECT
        Account.Name,
        CASE
            WHEN mm_payment.mm_paymentstatus = 'paid' THEN 'Paid'
            ELSE 'Unpaid'
        END AS Payment_Status
    FROM Account
    INNER JOIN mm_payment
        ON mm_payment.mm_organisation = Account.AccountId
)
SELECT
    Name,
    Payment_Status
FROM PaymentData
WHERE Payment_Status = 'Paid';

注意事项

SQL 字符串应使用单引号，例如 'paid'，不要写成 "paid"。
SQL 单行注释通常使用 --，不是 //。
字符串比较是否区分大小写，取决于数据库及其排序规则。应先确认实际存储的值是 paid、Paid 还是其他形式。
如果 mm_paymentstatus 实际上是选项集、枚举或整数状态码，CASE 应比较对应的底层代码，而不是界面上显示的文本。
如果所有非 paid 状态确实都表示未付款，可以简化为“paid 映射为 Paid，其余状态映射为 Unpaid”。否则应逐项列出状态，以免把退款、取消或未知状态错误地归为未付款。