SQL中PIVOT函数和CASE方法实现透视表的差异

为了解释清楚之间的差异，创建如下所示数据表

CREATE TABLE 销售
(商店名 VARCHAR(50),
 商品类型 VARCHAR(50),
 销售量 INT,
 日期 DATE)

INSERT INTO 销售
VALUES ('李宁','短袖',30,'2021/4/27'),
('李宁','短袖',40,'2021/4/28'),	
('李宁','短袖',50,'2021/4/29'),	
('李宁','裤子',60,'2021-4-27'),
('李宁','裤子',80,'2021-4-28'),
('李宁','裤子',90,'2021-4-29'),
('安踏','短袖',20,'2021-4-27'),
('安踏','短袖',61,'2021-4-28'),
('安踏','短袖',55,'2021-4-29'),
('安踏','裤子',56,'2021-4-27'),
('安踏','裤子',60,'2021-4-28'),
('安踏','裤子',78,'2021-4-29')

CASE 方法

• 统计这段时间内各个商店各商品类型销售总额，并将商品类型列中的值转换为列标题

SELECT 商店名,
SUM(CASE WHEN 商品类型='短袖' THEN 销售量 END ) AS 短袖,
SUM(CASE WHEN 商品类型='裤子' THEN 销售量 END ) AS 裤子
FROM 销售
GROUP BY 商店名

商店名    短袖    裤子
安踏       136     194
李宁       120     230

此时功能既有求和又有行列转换，商店名类似EXCEL透视表功能中的行，商品类型类似 EXCEL透视表功能中的列，销售量类似EXCEL透视表功能中的值

• 将商品类型列中的值转换为列标题

SELECT 商店名,日期,
SUM(CASE WHEN 商品类型='短袖' THEN 销售量 END ) AS 短袖,
SUM(CASE WHEN 商品类型='裤子' THEN 销售量 END ) AS 裤子
FROM 销售
GROUP BY 商店名,日期

商店名       日期         短袖  裤子
安踏      2021-04-27    20    56
李宁      2021-04-27    30    60
安踏      2021-04-28    61    60
李宁      2021-04-28    40    80
安踏      2021-04-29    55    78
李宁      2021-04-29    50    90 

此时功能表面上看仅有行列转换，虽然用到聚合函数进行求和，但实际上并没有数值上的变化，因为商店名和日期对应的短袖的销售数据只有1条，商店名和日期类似EXCEL透视表功能中的行，商品类型类似 EXCEL透视表功能中的列，销售量类似EXCEL透视表功能中的值

PIVOT函数

• 将商品类型列中的值转换为列标题

SELECT 商店名,[短袖],[裤子]
FROM 销售
PIVOT(SUM(销售量) FOR 商品类型 IN ([短袖],[裤子])) a

商店名   短袖    裤子
安踏        20    56
李宁        30    60
安踏        61    60
李宁        40    80
安踏        55    78
李宁        50    90

 明明SELECT语句中有商店名，怎么没有对商店名进行分组，为什么安踏和李宁出现三次，安踏短袖的销售数量不显示总和136，是不是有点像仅仅进行了行列转换？

这实际上与SQL语句的执行顺序有关，销售表中包含四列，分别为商店名，商品类型，销售量和日期，上述语句执行步骤类似如下：

1. 首先，对非聚合列进行分组，然后对聚合列进行统计

商店名,商品类型,销售日期为非聚合列，销售量为聚合列，商品类型既是非聚合列也是要成为列标题的列

SELECT 商店名,商品类型,销售日期,SUM(销售量)  FROM 销售 GROUP BY 商店名,商品类型, 销售日期

2.其次，将要成为列标题的值转化为透视列，值为聚合函数对应的值

具体为将商品类型列中的值转换为列标题，值为SUM(销售量）对应的值

3. 最后，从这个表中筛选出列

上述语句相当于筛选出商店名,短袖和裤子这三列

SELECT 商店名,日期,[短袖],[裤子]
FROM 销售
PIVOT(SUM(销售量) FOR 商品类型 IN ([短袖],[裤子])) a

商店名    日期    短袖    裤子
安踏    2021-04-27    20    56
李宁    2021-04-27    30    60
安踏    2021-04-28    61    60
李宁    2021-04-28    40    80
安踏    2021-04-29    55    78
李宁    2021-04-29    50    90 

SELECT 商店名,[短袖],[裤子] FROM 销售 PIVOT(SUM(销售量) FOR 商品类型 IN ([短袖],[裤子])) a 和 SELECT 商店名,日期,[短袖],[裤子] FROM 销售 PIVOT(SUM(销售量) FOR 商品类型 IN ([短袖],[裤子])) a本质上是一样的，只是最后筛选出的列不同，换句话说PIVOT函数会对源表中除聚合列以外的所有列进行分组GROUP BY，即使SELECT语句中没有出现一些非聚合列

为了实现这段时间内各个商店各商品类型销售总额，需要在源表上进行调整

• 统计这段时间内各个商店各商品类型销售总额，并将商品类型列中的值转换为列标题

SELECT 商店名,[短袖],[裤子]
FROM (SELECT 商店名,商品类型,销售量 FROM 销售) b
PIVOT(SUM(销售量) FOR 商品类型 IN ([短袖],[裤子])) a

商店名    短袖    裤子
安踏       136     194
李宁       120     230

综上，无论是利用CASE还是PIVOT进行透视功能，需要搞清楚是仅仅要实现行列转换，还是需要分组统计后再进行行列转换，不同的需求SQL语句会有差异，另外，对同一需求CASE和PIVOT的思路也是存在差异的