Pivot vs Case T-sql 效率

素材狗 2021-01-01 18:51:25 文章分类：数据库问题点击数：次

文章标签

Pivot vs Case T-sql efficiency(Pivot vs Case T-sql 效率)

本文介绍了Pivot vs Case T-sql 效率的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想弄清楚哪种方式更有效.我尝试使用 Sql Fiddle 但它似乎无法识别 PIVOT 或 UNION我可以查看哪些工具或统计数据来确定哪个更有效.

I am trying to figure out which way is more efficient. I tried to use Sql Fiddle but it didn't seem to recognize PIVOT or UNION What tools or statistics can I look at to determine which is more efficient.

CREATE TABLE T
(
  PersonNum INT
 ,WeekOf DATETIME
 ,ActivityType1 INT
 ,ActivityType2 INT
 ,Hours INT
)

INSERT INTO T VALUES(1,'11/2/2014',5,0,40)
INSERT INTO T VALUES(1,'11/2/2014',1,0,5)
INSERT INTO T VALUES(1,'11/2/2014',1,0,8)
INSERT INTO T VALUES(1,'11/2/2014',2,1,6)
INSERT INTO T VALUES(1,'11/2/2014',2,2,2)
INSERT INTO T VALUES(1,'11/2/2014',2,3,9)
INSERT INTO T VALUES(1,'11/2/2014',2,4,7)

INSERT INTO T VALUES(1,'11/9/2014',5,0,40)
INSERT INTO T VALUES(1,'11/9/2014',1,0,2)
INSERT INTO T VALUES(1,'11/9/2014',1,0,6)
INSERT INTO T VALUES(1,'11/9/2014',2,1,7)
INSERT INTO T VALUES(1,'11/9/2014',2,2,2)
INSERT INTO T VALUES(1,'11/9/2014',2,3,3)
INSERT INTO T VALUES(1,'11/9/2014',2,4,5)

方法一

SELECT 
  PersonNum
, WeekOf
, SUM(CASE WHEN ActivityType1 = 5 THEN Hours ELSE 0 END) AS Beginning
, SUM(CASE WHEN ActivityType1 = 1 THEN Hours ELSE 0 END) AS Plus
, SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 <> 3 THEN Hours ELSE 0 END) AS Minus
, SUM(CASE WHEN ActivityType1 = 2 AND ActivityType2 = 3 THEN Hours ELSE 0 END) AS MinusSpecial
FROM T
GROUP BY 
  PersonNum
, WeekOf

方法二

SELECT
  PersonNum
, WeekOf
, [Beginning]
, [Plus]
, [Minus]
, [Minus Special]
FROM
(
  SELECT
    PersonNum
  , WeekOf
  , 'Beginning' AS ColumnType
  , Hours
  FROM T
  WHERE
    ActivityType1 = 5 
  UNION
  SELECT
    PersonNum
  , WeekOf
  , 'Plus' AS ColumnType
  , Hours
  FROM T
  WHERE
    ActivityType1 = 1 
  UNION
  SELECT
    PersonNum
  , WeekOf
  , 'Minus' AS ColumnType
  , Hours
  FROM T
  WHERE
      ActivityType1 = 2 
  AND ActivityType2 <> 3
   UNION
  SELECT
    PersonNum
  , WeekOf
  , 'Minus Special' AS ColumnType
  , Hours
  FROM T
  WHERE
      ActivityType1 = 2 
  AND ActivityType2 = 3
    ) Data  
PIVOT 
(SUM(Hours)
FOR ColumnType IN ([Beginning]                
                  ,[Plus]
                  ,[Minus]
                  ,[Minus Special])
) pvt