/*

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