Northwind每个客户的总订单

Question

我将列TotalOrders添加到Customers表中。

ALTER TABLE customers ADD TotalOrders INT NULL

我试图找出每个客户的订单总数，并把这个价值加到这个专栏中，但是我不知道到底需要什么来加和

INSERT INTO customers (TotalOrders) SELECT SUM(...)

Answer 1

我觉得你应该在这里使用更新。您可以在orders表中汇总每个客户的订单总数，然后使用这些信息更新customers表。

UPDATE t1
SET TotalOrders = t2.TotalOrders
FROM customers t1
INNER JOIN
(
    SELECT CustomerID, COUNT(*) AS TotalOrders
    FROM orders
    GROUP BY CustomerID
) t2
    ON t1.CustomerID = t2.CustomerID

Answer 2

INSERT INTO customers (TotalOrders) 
VALUES(SELECT SUM(Orders.Total)  
FROM Orders INNER JOIN Customers ON Orders.CustomerId=
Customer.Id Group By  Customer.Id) WHERE Customer.Id =Orders.CustomerId