我试图找出哪种方式更有效率。 我尝试使用Sql Fiddle,但它似乎无法识别PIVOT或UNION我可以查看哪些工具或统计数据来确定哪个更有效。
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)
方法 1
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
方法 2
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
我可以
建议重写您的 PIVOT 查询,这样您就不必点击表格 5 次。
select P.PersonNum,
P.WeekOf,
P.Beginning,
P.Plus,
P.Minus,
P.MinusSpecial
from (
select T.PersonNum,
T.WeekOf,
T.Hours,
case ActivityType1
when 5 then 'Beginning'
when 1 then 'Plus'
when 2 then case ActivityType2
when 3 then 'MinusSpecial'
else 'Minus'
end
end as ColumnType
from T
) as T
pivot (sum(T.Hours) for T.ColumnType in (Beginning, Plus, MinusSpecial, Minus)) as P
这样,您可能会通过查询获得与您的组相同的查询计划,并且性能大致相同。
你可以这样做
set statistics time on
--first query
set statistics time off
set statistics time on
--second query
set statistics time off
您可以查看"消息"窗口以查看执行时间
方法 1 肯定会更快。这是因为只有 1 个选择语句。另一方面,方法 2 中有 5 个选择语句。一般规则是,查询包含的语句越多,执行所需的时间就越长。