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

本文通过一个SQL查询优化案例展示了如何减少对同一表的多次查询，从而提高数据库操作效率。原本的查询方式每次仅针对不同的组合条件进行单一查询，优化后的方案采用一次查询获取所有所需数据，有效减少了对表的占用时间。

/*
server:
db: EDI
*/
-- 以下案例多次查询同一张表，仅有组合条件Name+Direction不同
--可以使用一次查出相关类容避免长时间占用表
USE EDI
GO
DECLARE @OutBoundBusinessID_PO int 
       ,@InboundBusinessID_ItemCatalog int 
       ,@InboundBusinessID_Inventory int 
       ,@InboundBusinessID_ShipNotice int                                       
	   ,@InboundBusinessID_FunctionAck int 
	   ,@OutboundBusinessID_FunctionAck int

SELECT TOP 1 
    @OutBoundBusinessID_PO = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'PO' 
    AND Direction = 'O' 
SELECT TOP 1 
    @InboundBusinessID_ItemCatalog = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'ItemCatalog'
    AND Direction = 'I'  
SELECT TOP 1 
    @InboundBusinessID_Inventory = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'Inventory' 
    AND Direction = 'I'  
SELECT TOP 1 
    @InboundBusinessID_ShipNotice = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'ShipNotice' 
    AND Direction = 'I'  
SELECT TOP 1 
    @InboundBusinessID_FunctionAck = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'FunctionAck' 
    AND Direction = 'I'
SELECT TOP 1 
    @OutboundBusinessID_FunctionAck = ID 
FROM dbo.EDI_CFG_Business WITH(NOLOCK) 
WHERE 
    Name = 'FunctionAck' 
    AND Direction = 'O'
select  @OutBoundBusinessID_PO 
       ,@InboundBusinessID_ItemCatalog  
       ,@InboundBusinessID_Inventory  
       ,@InboundBusinessID_ShipNotice                                        
	   ,@InboundBusinessID_FunctionAck  
	   ,@OutboundBusinessID_FunctionAck 

-----更改后
USE EDI
GO
DECLARE @OutBoundBusinessID_PO int 
       ,@InboundBusinessID_ItemCatalog int 
       ,@InboundBusinessID_Inventory int 
       ,@InboundBusinessID_ShipNotice int                                       
	   ,@InboundBusinessID_FunctionAck int 
	   ,@OutboundBusinessID_FunctionAck int

select @OutBoundBusinessID_PO=POO
	,@InboundBusinessID_ItemCatalog=ItemCatalogI
	,@InboundBusinessID_Inventory=InventoryI
	,@InboundBusinessID_ShipNotice=ShipNoticeI
	,@InboundBusinessID_FunctionAck=FunctionAckI
	,@OutboundBusinessID_FunctionAck=FunctionAckO
--select POO,ItemCatalogI,InventoryI,ShipNoticeI,FunctionAckI,FunctionAckO
from
(
	select ID,Name_Direction
	from
	(
		select ID,Name+Direction as Name_Direction FROM dbo.EDI_CFG_Business WITH(NOLOCK)
		where 
		Name in('PO','ItemCatalog','Inventory','ShipNotice','FunctionAck')
		and  Direction in('I','O')
	) as T1
	where Name_Direction in('POO','ItemCatalogI','InventoryI','ShipNoticeI','FunctionAckI','FunctionAckO')
) as T2
pivot
(
	max(ID)
	for
	Name_Direction in([POO],[ItemCatalogI],[InventoryI],[ShipNoticeI],[FunctionAckI],[FunctionAckO]) 

) as piv


select  @OutBoundBusinessID_PO 
       ,@InboundBusinessID_ItemCatalog  
       ,@InboundBusinessID_Inventory  
       ,@InboundBusinessID_ShipNotice                                        
	   ,@InboundBusinessID_FunctionAck  
	   ,@OutboundBusinessID_FunctionAck

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