/* 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