每位客户的Northwind SQL总订单 [英] Northwind SQL total orders per customer

问题描述

我已经在Customers表中添加了列TotalOrders.

I've added a column TotalOrders to the Customers table.

ALTER TABLE customers ADD TotalOrders INT NULL

我正在尝试查找每位客户的订单总数，并将该值添加到此列中，但是我无法弄清楚我需要精确地求和什么

I'm trying to find the total number of orders per customer and add that value to this column, however i can't figure out what do i need to sum exactly

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

推荐答案

我认为您应该在此处使用 update .您可以在orders表中汇总每个客户的订单总数，然后使用此信息更新customers表.

I think you should be using an update here. You can aggregate the total number of orders per customer in the orders table, and then update the customers table with this information.

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

这篇关于每位客户的Northwind SQL总订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！