选择客户所有车辆的总账面价值 (SQL)

Question

我有以下数据库架构（Microsoft SQL Server Compact Edition）：

如何选择一条或多条客户记录，是否有一个附加字段包含客户拥有的所有车辆的总账面价值？我尝试了类似的操作：

SELECT Customers.ID, Customers.FirstName, ..., SUM(Receipts.BookValue) FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN Receipts ON Receipts.VehicleID = Vehicles。 ID;

但没有成功。我想我可能需要 GROUP BY 之类的东西？

Answer 1

SELECT Customers.ID, SUM(Receipts.BookValue) FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN Receipts ON Receipts.VehicleID = Vehicles.ID
GROUP BY Customers.ID

另请注意，当使用 Sum 等聚合时，您必须确保所有附加字段都是聚合类型的一部分或在 GROUP BY 列表中。

有时进行 2 个查询更容易，内部查询执行所有求和，其余查询显示附加信息，如下所示：

SELECT t.TheSum,
       c.ID,
       c.CompanyName FROM
   ( SELECT Customers.ID as TheID, 
           SUM(Receipts.BookValue) AS TheSum
    FROM Customers INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID INNER JOIN    Receipts ON Receipts.VehicleID = Vehicles.ID
    GROUP BY Customers.ID
) t
INNER JOIN Customers c ON c.ID = t.TheID

Answer 2

是的，您需要一个 GROU BY <不是聚合的每列的列表>（SUM、COUNT 等）

Answer 3

select c.ID, c.FirstName, c.LastName, bv.BookValueSum
from Customers c
inner join (
    select v.CustomerID, sum(r.BookValue) as BookValueSum
    from Vehicles v
    inner join Receipts r on v.ID = r.VehicleID
    group by v.CustomerID
) bv on c.CustomerID = bv.CustomerID

Answer 4

如果您想要账面价值聚合和所有客户字段。像这样的东西会起作用

SELECT 
  C.*,
  BV.TotalBookValue
FROM Customers C
Inner Join (SELECT 
              Customers.ID as CustomerID, 
              SUM(Receipts.BookValue) AS TotalBookValue
            FROM Customers 
            INNER JOIN Vehicles ON Vehicles.CustomerID = Customers.ID 
            INNER JOIN Receipts ON Receipts.VehicleID = Vehicles.ID
            GROUP BY Customers.ID) BV 
   ON C.ID = BV.CustomerID