我有一张表,有两个列: sales(int)
和 month(int)
。我想要检索每个月对应的销售总额。输出需要以每个月为对应的12个列的形式呈现,其中每个列(month)只包含一个记录。
我有一张表,有两个列: sales(int)
和 month(int)
。我想要检索每个月对应的销售总额。输出需要以每个月为对应的12个列的形式呈现,其中每个列(month)只包含一个记录。
你应该看一下PIVOT,以将行与列进行转换。这样可以避免为每个月编写一个select语句。示例代码如下:
DECLARE @salesTable TABLE
(
[month] INT,
sales INT
)
-- Note that I use SQL Server 2008 INSERT syntax here for inserting
-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)
SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM
(
SELECT [month], sales
FROM @salesTable
) AS SourceTable
PIVOT
(
SUM(sales)
FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable
虽然不太美观,但这个方案非常有效
SELECT
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 1) [Sales1],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 2) [Sales2],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 3) [Sales3],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 4) [Sales4],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 5) [Sales5],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 6) [Sales6],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 7) [Sales7],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 8) [Sales8],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 9) [Sales9],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 10) [Sales10],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 11) [Sales11],
(SELECT SUM(Sales) FROM SalesTable WHERE [Month] = 12) [Sales12]
select
Sales1 = sum( case when Month = 1 then Sales end )
, Sales2 = sum( case when Month = 2 then Sales end )
, Sales3 = sum( case when Month = 3 then Sales end )
-- etc..
from SalesTable;
我进行了一些调查,似乎新的枢轴运算符只是这种类型查询的语法糖。查询计划最终看起来完全相同。
有趣的是,反枢轴运算符似乎也只是语法糖。例如:
如果你有一个表格像这样:
Create Table Sales ( JanSales int, FebSales int, MarchSales int...)
您可以编写:
select unpivoted.monthName, unpivoted.sales
from Sales s
outer apply (
select 'Jan', JanSales union all
select 'Feb', FebSales union all
select 'March', MarchSales
) unpivoted( monthName, sales );