SQL一次查出相关类容避免长时间占用表(下)-优快云博客

本文通过一个SQL优化案例展示了如何减少对同一表的多次查询，通过使用Pivot操作一次性获取多个文件类型ID，以此来减少数据库资源的占用。

/*
server:
db: EDI
*/
-- 以下案例多次查询同一张表，仅有Name条件不同
--可以使用一次查出相关类容避免长时间占用表
USE EDI
GO
DECLARE @FileTypeID_X12850 int 
       ,@FileTypeID_X12832 int 
       ,@FileTypeID_X12846 int 
SELECT TOP 1 
    @FileTypeID_X12850 = ID 
FROM dbo.EDI_CFG_FileType WITH(NOLOCK) 
WHERE 
    Name='X12850' 
SELECT TOP 1 
    @FileTypeID_X12832 = ID 
FROM dbo.EDI_CFG_FileType WITH(NOLOCK) 
WHERE 
    Name='X12832' 
SELECT TOP 1 
    @FileTypeID_X12846 = ID 
FROM dbo.EDI_CFG_FileType WITH(NOLOCK) 
WHERE 
    Name='X12846' 



---更改后

use EDI
go

DECLARE @FileTypeID_X12850 int 
       ,@FileTypeID_X12832 int 
       ,@FileTypeID_X12846 int 

select @FileTypeID_X12850=X12850
		,@FileTypeID_X12832=X12832
		,@FileTypeID_X12846=X12846

--select X12850,X12832
from
	(
		select ID,Name from dbo.EDI_CFG_FileType WITH(NOLOCK) 
		where Name in('X12850','X12832','X12846')
	) as tem
pivot
(
	max(ID)
	for
	Name in ([X12850],[X12832],[X12846])
)
as piv
select @FileTypeID_X12850
	,@FileTypeID_X12832
	,@FileTypeID_X12846

转载于:https://www.cnblogs.com/cykj/p/SQL-Avoid-occupation-downn.html