SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2469字，纯文字阅读大概需要4分钟。
内容图文
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行列转换减少扫描计数优化查询语句
标签：
本文系统来源：http://www.cnblogs.com/wy123/p/5933734.html
内容总结
以上是互联网集市为您收集整理的SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句全部内容，希望文章能够帮你解决SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】