使用子查询对关联模型的列求和，为所有父项返回相同的数量

Question

给定模型User和Invoice，用户有许多发票，而发票属于用户。

发票有status和amount_cents列。

我需要编写一个查询，获取所有的User列，但也添加以下列：

• 一个total_paid别名列，用于汇总每个用户的所有paid发票的amount_cents
• total_unpaid别名列，用于对每个用户的所有unpaid发票的amount_cents求和

当使用我为其分配别名的多个子查询时，我有点不知道正确的结构是什么，但我已经为任务的第一部分想出了一些非常基本的东西：

select users.*, (SELECT SUM(amount_cents) FROM invoices) as total_paid from users
join invoices on users.id = invoices.user_id
where invoices.status = 'paid'
group by users.id

我不确定我是应该从父端还是子端编写查询(我想是从父(用户(端编写的，因为我需要的所有数据都在users列中(，但上面的查询似乎在total_paid列中为所有不同的用户返回了相同的金额，而不是为每个用户返回正确的金额。

如有任何帮助，我们将不胜感激。

Answer 1

语句(SELECT SUM(amount_cents(FROM发票(返回所有用户的总金额，这与您想要的每个用户的金额不同：

具有横向联接的解决方案：

select u.*
, paid.total as total_paid
, unpaid.total as total_unpaid
FROM users AS u
LEFT JOIN LATERAL
( SELECT sum(amount_cents) AS total
FROM invoices
WHERE user_id = u.id
AND status = 'paid'
) AS paid
ON True
LEFT JOIN LATERAL
( SELECT sum(amount_cents) AS total
FROM invoices
WHERE user_id = u.id
AND status = 'unpaid'
) AS unpaid
ON True

具有JOIN&窗口功能：

SELECT u.*
, t.total_paid
, t.total_unpaid
FROM users AS u
INNER JOIN 
(
SELECT DISTINCT ON (user_id)
, user_id
, sum(amount_cents) FILTER (WHERE status = 'paid') OVER (PARTITION BY user_id  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_paid
, sum(amount_cents) FILTER (WHERE status = 'unpaid') OVER (PARTITION BY user_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_unpaid
FROM invoices
ORDER BY u.user_id
) AS t
ON u.id = t.user_id

Answer 2

您可以使用标量子查询

select u.*, 
(select sum(amount_cents) from invoices where user_id = u.id and status = 'paid') total_paid,
(select sum(amount_cents) from invoices where user_id = u.id and status = 'unpaid') total_unpaid
from users u;

或者可能更有效的横向连接。

select u.*, l.*
from users u
left join lateral 
(
select sum(amount_cents) filter (where status = 'paid') total_paid,
sum(amount_cents) filter (where status = 'unpaid') total_unpaid
from invoices where user_id = u.id
) l on true;

然而，如果users.id是主键(可能是这样(，那么事情可以简化为

select u.*, 
sum(i.amount_cents) filter (where i.status = 'paid') total_paid,
sum(i.amount_cents) filter (where i.status = 'unpaid') total_unpaid 
from users u
left outer join invoices i on u.id = i.user_id
group by u.id;

Answer 3

这可以使用如下子查询来完成：

Select users.id,
(Select Sum(amount_cents) 
From invoices Where status = 'paid' And user_id=users.id) As total_paid,
(Select Sum(amount_cents)
From invoices Where status = 'unpaid' And user_id=users.id) As total_unpaid
From users
Group by users.id

Answer 4

另一种选择是使用外部连接

users_table = User.arel_table
paid_invoices_table = Arel::Table.new(Invoice.arel_table.name, as: 'paid_invoices')
unpaid_invoices_table = Arel::Table.new(Invoice.arel_table.name, as: 'unpaid_invoices')
paid_join = Arel::Nodes::OuterJoin.new(
paid_invoices_table,
Arel::Nodes::On.new(
users_table[:id].eq(paid_invoices_table[:user_id])
.and(paid_invoices_table[:status].eq('paid'))
)
)
unpaid_join = Arel::Nodes::OuterJoin.new(
unpaid_invoices_table,
Arel::Nodes::On.new(
users_table[:id].eq(unpaid_invoices_table[:user_id])
.and(unpaid_invoices_table[:status].not_eq('paid'))
)
)
User.joins(paid_join,unpaid_join)
.select(
User.arel_table[Arel.star],
paid_invoices_table[:amount_cents].sum.as('total_paid'), 
unpaid_invoices_table[:amount_cents].sum.as('total_unpaid'))   
.group(:id)

结果查询：

SELECT 
users.*,
SUM(paid_invoices.amount_cents) AS total_paid,
SUM(unpaid_invoices.amount_cents) AS total_unpaid
FROM 
users 
LEFT OUTER JOIN invoices AS paid_invoices ON users.id = paid_invoices.user_id
AND paid_invoices.status = 'paid' 
LEFT OUTER JOIN invoices AS unpaid_invoices ON users.id = unpaid_invoices.user_id
AND unpaid_invoices.status <> 'paid'
GROUP BY 
users.id