使用约束中的变量多次运行脚本

Question

有没有一种方法可以填充 Table2 中的 Cust1、Cust2、Cust3 列，而不涉及运行相同的查询几个不同的“WHERE”子句？

例如，现在我会这样做：

INSERT INTO Table2(Cust1_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 1
GROUP BY Fruit

INSERT INTO Table2(Cust2_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 2
GROUP BY Fruit

INSERT INTO Table2(Cust3_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 3
GROUP BY Fruit

理想情况下，我希望有另一个表，其中包含所有唯一客户的 Customer_ID 列，然后我将在上述查询的循环中引用每个客户。 SQL Server 2008。

Answer 1

您可以使用 case 在单独的列中生成每个客户的总和：

INSERT  Table2
        (Produce, Cust1_Totals, Cust2_Totals, Cust3_Totals)
SELECT  Fruit
,       SUM(case when Customer_ID = 1 then quantity end)
,       SUM(case when Customer_ID = 2 then quantity end)
,       SUM(case when Customer_ID = 3 then quantity end)
FROM    Table1
WHERE   Customer_ID IN (1,2,3)
GROUP BY 
        Fruit

生成可变数量的列将需要动态 SQL。通常让 Excel 更容易做到这一点。在 Excel 中，这称为“旋转”。

Answer 2

也许类似于：

select * from table1
pivot (sum(quantity) for customerid in ([1], [2], [3])) as pivotTable

但您仍然必须在此处指定客户 ID。