給定模型User和Invoice，一個用戶有很多發票，一張發票屬于一個用戶。
發票有status和amount_cents列。
我需要撰寫一個查詢來獲取所有用戶列，但還要添加以下列：
- 一個
total_paid別名列，匯總每個用戶amount_cents的所有paid發票 - 一個
total_unpaid別名列，匯總每個用戶amount_cents的所有unpaid發票
我有點迷失在使用多個子查詢時正確的結構是什么，我為其分配了別名，但我已經為任務的第一部分提出了一些非常基本的東西：
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
我不確定我是否應該從父或子端撰寫查詢（我想是從父（用戶）端撰寫查詢，因為我需要的所有資料都在用戶列中）但上面的查詢似乎回傳相同量在total_paid所有不同的用戶，而不是適量為每個用戶列。
任何幫助，將不勝感激。
uj5u.com熱心網友回復：
陳述句 (SELECT SUM(amount_cents) FROM invoices) 回傳所有用戶的總金額，這與您想要的每個用戶的金額不同：
使用 LATERAL JOIN 的解決方案：
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
uj5u.com熱心網友回復：
您可以使用標量子查詢
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;
uj5u.com熱心網友回復：
這可以使用子查詢來完成，如下所示：
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
uj5u.com熱心網友回復：
另一種選擇是使用外部聯接
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/376009.html
標籤：sql 红宝石轨道 数据库 PostgreSQL的 表现