使用子查询求和关联模型列时所有用户返回相同金额的问题咨询

问题根源 #

你现有SQL的错误来自两个核心逻辑问题：

1. 子查询未关联外层用户ID：(SELECT SUM(amount_cents) FROM invoices) 统计的是全表所有发票的总金额，没有限定到当前行的用户，所以所有用户的total_paid返回值都相同。
2. 外层WHERE invoices.status = 'paid'过滤规则会直接筛掉没有已支付发票的用户，同时也无法统计未支付发票的金额。

最优实现方案（条件聚合+左连接，执行效率更高） #

SELECT 
    users.*,
    SUM(CASE WHEN invoices.status = 'paid' THEN invoices.amount_cents ELSE 0 END) AS total_paid,
    SUM(CASE WHEN invoices.status = 'unpaid' THEN invoices.amount_cents ELSE 0 END) AS total_unpaid
FROM users
LEFT JOIN invoices ON users.id = invoices.user_id
GROUP BY users.id

说明：

• 用LEFT JOIN代替内连接，保证没有任何发票的用户也能正常返回
• CASE WHEN按发票状态分别统计金额，ELSE 0规则让没有对应状态发票的用户返回0而非NULL，不需要0可以去掉该规则
• 仅需一次关联查询，比多次子查询性能更好

关联子查询写法（符合你最初的写法思路） #

如果你更偏好子查询实现，可以按如下规则修正：

SELECT 
    users.*,
    COALESCE((SELECT SUM(amount_cents) FROM invoices WHERE user_id = users.id AND status = 'paid'), 0) AS total_paid,
    COALESCE((SELECT SUM(amount_cents) FROM invoices WHERE user_id = users.id AND status = 'unpaid'), 0) AS total_unpaid
FROM users

说明：

• 每个子查询增加user_id = users.id关联条件，限定只统计当前行用户的发票
• COALESCE函数用于把无对应发票时返回的NULL转换为0，不需要0可以去掉该函数包装

内容的提问来源于stack exchange，提问作者pinkfloyd90