計算嵌套的一對多關係的總數

表：Users, Costs,Payments

使用者可能有很多成本。成本可以有很多付款

我想獲取有關使用者的資訊以及他們支付的所有款項的數量

此查詢返回結果但需要很長時間才能執行

SELECT u.id, u.name,
 (
   SELECT COUNT(p.id)
   FROM payments AS p
   WHERE p.cost_id IN (SELECT cost_id FROM costs WHERE user_id = u.id)
 ) AS payments_made
FROM users as u;

我嘗試了外部聯接，但如果使用者有多個成本，它會返回重複

內連接刪除了所有沒有成本的使用者

讓我知道這是否涵蓋了您想要的結果。

create table users (user_id int primary key, name varchar(20));
insert into users values(1, 'john'), (2, 'anne'), (3, 'peter');

create table costs(cost_id int primary key, user_id int);
insert into costs values (1, 1), (2, 1), (3, 2);

create table payments(payment_id int primary key, cost_id int);
insert into payments values
(1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 3);
GO

13 行受影響

select   c.user_id, count(*) num_payments
from     payments p
join     costs c
on       p.cost_id = c.cost_id
group by c.user_id
;
GO

使用者 ID | 支付數
------: | -----------:
 1 | 5
 2 | 2

select    u.user_id, u.name, coalesce(p.num_payments, 0) num_payments
from      users u
left join (select   c.user_id, count(*) num_payments
           from     payments p
           join     costs c
           on       p.cost_id = c.cost_id
           group by c.user_id) p
on         u.user_id = p.user_id           
GO

使用者 ID | 姓名 | 支付數
------: | :---- | -----------:
 1 | 約翰 | 5
 2 | 安妮 | 2
 3 | 彼得 | 0

dbfiddle在這裡

或者更好的左連接解決方案：

select    u.user_id, u.name, count(p.payment_id) num_payments
from      users u
left join costs c
on        u.user_id = c.user_id
left join payments p
on        p.cost_id = c.cost_id
group by  u.user_id, u.name
;
GO

使用者 ID | 姓名 | 支付數
------: | :---- | -----------:
 1 | 約翰 | 5
 2 | 安妮 | 2
 3 | 彼得 | 0

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/197203