本文介绍了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
推荐答案
你可以这样做
set statistics time on
--first query
set statistics time off
set statistics time on
--second query
set statistics time off
您可以在消息窗口中查看执行时间
You can look in the Messages window to see the execution times
这篇关于Pivot vs Case T-sql 效率的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!