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 条评论
登录 后参与评论

相关文章

来自专栏kwcode

SQL语句帮助大全

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

34611
来自专栏码农二狗

mysql实现地理位置搜索

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

SQL将本地图片文件插入到数据库

2332
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

6442
来自专栏james大数据架构

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

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

1928
来自专栏一个会写诗的程序员的博客

JPA 执行update/delete query 需要加上事务

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Executing...

1052
来自专栏Java Web

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

3194
来自专栏c#开发者

Paging of Large Resultsets in ASP.NET

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

43511
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1343
来自专栏「3306 Pai」社区

不用MariaDB/Percona也能查看DDL的进度

使用MariaDB/Percona版本的一个便利之处就是可以及时查看DDL的进度,进而预估DDL耗时。 其实,在官方版本里也是可以查看DDL进度的,认真看手册的...

1910

扫码关注云+社区