SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
标签：
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
table HeaderTable
(
HeaderId int ,
OtherColumn varchar(50)
)
create table DetailTable
(
HeaderId int,
DetailId int identity(1,1),
DetailKey varchar(50),
DetailValues int
)
declare @i int = 0
while @i<1000000
begin
insert into HeaderTable values (@i,NEWID())
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0001‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0002‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0003‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0004‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0005‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0006‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0007‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0008‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0009‘,RAND()*10000)
insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0010‘,RAND()*10000)
set @i=@i+1
end
create index idx_HeaderId on HeaderTable(HeaderId)
create index idx_HeaderId on DetailTable(HeaderId)
create index idx_DetailKey on DetailTable(DetailKey)
select *,
(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0001‘) as ‘Key1的值‘,
(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0002‘) as ‘Key2的值‘,
(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0003‘) as ‘Key3的值‘,
(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0004‘) as ‘Key4的值‘,
(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and t.DetailKey = ‘A0005‘) as ‘Key5的值‘
from HeaderTable a
where a.HeaderId = 10000
SELECT
a.*,
t.A0001 as ‘Key1的值‘,
t.A0002 as ‘Key2的值‘,
t.A0003 as ‘Key3的值‘,
t.A0004 as ‘Key4的值‘,
t.A0005 as ‘Key5的值‘
from HeaderTable a inner join
(select HeaderId ,DetailKey ,DetailValues from DetailTable)t
pivot( MAX(DetailValues) FOR DetailKey IN (A0001,A0002,A0003,A0004,A0005)
)t on t.HeaderId = a.HeaderId
where a.HeaderId = 10000