代码之家 › 专栏 › 技术社区 › Jake Petroules

为客户选择所有车辆的帐面总值(SQL)

sql-server-ce sql-server sql

Jake Petroules · 技术社区 · 15 年前

我有以下数据库架构(Microsoft SQL Server Compact版本):

alt text

如何选择一个或多个客户记录,以及包含客户拥有的所有车辆的总帐面价值的附加字段?我试过这样的方法:

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;

但没有成功。我想我可能需要一个小组什么的?

4 回复 | 直到 15 年前

JonH 15 年前

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列表中。

有时进行两个查询更容易,内部查询完成所有的求和,其余的显示如下附加信息:

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

Paulo Scardine 15 年前

是的,你需要一个口交 <list of every column that is not an aggregate> (总数、计数等)

D'Arcy Rittich 15 年前

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

John Hartsock 15 年前

如果需要帐面价值合计和所有客户字段。像这样的东西会有用的

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

推荐文章