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 

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏跟着阿笨一起玩NET

(1)显示每个类别最新更新的数据

在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据。特写下这个sql记录于此:

581
来自专栏Java Web

模仿天猫实战【SSM版】——项目起步

2674
来自专栏kwcode

SQL语句帮助大全

--删除约束 Status:字段名 alter table Table_1 drop constraint Status; --添加约束 --Status :字...

34111
来自专栏码农二狗

mysql实现地理位置搜索

37910
来自专栏james大数据架构

你真的会玩SQL吗?和平大使 内连接、外连接

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

1868
来自专栏JetpropelledSnake

SQL学习笔记之MySQL查询练习2

github地址: https://github.com/nql1314/sql-practises

722
来自专栏简书专栏

mysql实训

设有一个数据库,包括四个表:学生表(student)、课程表(course)、成绩表(score)以及教师信息表(teacher)。用SQL语句创建四个表并完成...

1031
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进...

2576
来自专栏c#开发者

Paging of Large Resultsets in ASP.NET

The paging of a large database resultset in Web applications is a well known pro...

42911
来自专栏james大数据架构

通用分页存储过程

/*通用分页存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WH...

2038

扫码关注云+社区