子查询还是不写这个查询？

My SQL query:

我的SQL查询:

select 
    *, 
    (select sum(Amount) as TotalAmount 
     from Payment 
     where Customer.Id = Payment.CustomerId) 
from Customer

The result:

Id - CustomerName - CustomerPhoneNumber - TotalAmount
1  - TestUser     - TestLastName        - 3000   
2  - TestUser2    - TestLastName2       - 2500

This is working as I expected. But are there any better ways to get same result ?

这是我预期的工作。但有没有更好的方法来获得相同的结果?

2 个解决方案

#1

If it's MySQL, then you can do this...

如果它是MySQL,那么你可以这样做......

SELECT
  Customer.*,
  SUM(Payment.Amount) AS TotalAmount
FROM
  Customer
LEFT JOIN
  Payment
    ON Payment.CustomerId = Customer.Id
GROUP BY
  Customer.id

In other varieties of SQL, you need to GROUP BY everything that is selected but not an aggregate.

在其他各种SQL中,您需要GROUP BY所有选定但不是聚合的内容。

SELECT
  Customer.Id,
  Customer.Name,
  Customer.PhoneNumber,
  SUM(Payment.Amount) AS TotalAmount
FROM
  Customer
LEFT JOIN
  Payment
    ON Payment.CustomerId = Customer.Id
GROUP BY
  Customer.Id,
  Customer.Name,
  Customer.PhoneNumber

#2

You can rewrite it with LEFT JOIN and GROUP BY :

您可以使用LEFT JOIN和GROUP BY重写它:

select 
a.*, sum(b.Amount) as TotalAmount    
from Customer a
left join Payment on (a.Id = b.CustomerId)
GROUP BY a.Id -- for mysql only, for other RDMS you have to list all Customer fields

 select 
a.*, b.TotalAmount    
from Customer a
left join 
(SELECT CustomerId, SUM(Amount) as TotalAmount 
 FROM Payment GROUP BY CustomerId)b  ON (b.CustomerId = a.Id)

#1