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
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
标签：
