主页 / dba / 问题 / 197203
Accepted
Ruslan
Asked: 2018-02-07 11:56:57 +0800 CST

计算嵌套一对多关系的总数

  • 772

表：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;

我尝试了外部连接，但如果用户有多个成本，它会返回重复项

内部连接丢弃了所有没有成本的用户

performance db2

1 个回答

  1. Best Answer

    让我知道这是否涵盖您想要的结果。

    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在这里

    • 1

相关问题