【工作中的常用SQL】客户最近三年的销售额

假设有一个名为Sales的表，包含以下列：CustomerID、OrderDate、OrderAmount。

可以使用以下SQL语句来生成透视表：

SELECT CustomerID, SUM(CASE WHEN YEAR(OrderDate) = YEAR(GETDATE()) THEN CASE WHEN MONTH(OrderDate) = 1 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 2 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 3 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 4 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 5 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 6 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 7 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 8 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 9 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 10 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 11 THEN OrderAmount ELSE 0 END, CASE WHEN MONTH(OrderDate) = 12 THEN OrderAmount ELSE 0 END, ELSE OrderAmount END) AS SalesAmount, YEAR(OrderDate) AS SalesYearFROM SalesWHERE OrderDate >= DATEADD(YEAR, -3, GETDATE())GROUP BY CustomerID, YEAR(OrderDate)

这个查询将返回一个透视表，其中每个客户在最近三年内的销售额被汇总。最近一年的销售额将分列12个月，而其他两年的销售额将显示为总额。