欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关于存储过程/函数/insert select/update select JOIN的sql应用例子

程序员文章站 2022-05-01 11:29:38
...

今天,有两张表需要互相同步数据,1对多的形式,写了一个过程,用到了存储过程/insert select/update select JOIN等复杂SQL
,数据库是sqlserver,其他库写法有微调.先记录如下:

ALTER PROCEDURE [dbo].[proc_export_order_return]
AS
BEGIN
  DECLARE @code nvarchar(1000);
	DECLARE @return_status nvarchar(10);
	DECLARE @return_time nvarchar(17);
	DECLARE @return_info nvarchar(1000);
	DECLARE @i bigint;
	DECLARE @j bigint;
	create table #t(
		rn int,
		code nvarchar(1000),
		return_status nvarchar(10),
		return_time nvarchar(17),
		return_info nvarchar(1000)
	);
	INSERT INTO #t
	SELECT rn=row_number() over(order by code asc),code,return_status,
	a.return_time,return_info FROM t_export_order_receipt b
	RIGHT  JOIN
	(SELECT ebc_code + '-' + order_no code, MAX(return_time) return_time FROM t_export_order_receipt WHERE remove = 0 
	GROUP BY ebc_code + '-' + order_no) a 
	ON a.code = b.ebc_code + '-' + b.order_no AND a.return_time = b.return_time
	SET @i = 1;
	SET @j = (SELECT COUNT(*) FROM #t )
	WHILE @i< = @j BEGIN
		-- 查询业务主键
		SET @code = (SELECT code FROM #t a WHERE a.rn = @i);
		-- 查询最新回执时间
		SET @return_time = (SELECT return_time FROM #t a WHERE a.rn = @i);
		-- 查询最新回执状态
		SET @return_status = (SELECT return_status FROM #t a WHERE a.rn = @i);
		-- 查询最新回执信息
		SET @return_info = (SELECT return_info FROM #t a WHERE a.rn = @i);
		--更新表头
		UPDATE t_export_order_head SET return_info = @return_info, return_time = @return_time, return_status = @return_status 
		WHERE ebc_code + '-' + order_no = @code AND remove = 0;
		SET @i = @i+1;
	END-- routine body goes here, e.g.

  -- 下面是更新回执表的headId,根据情况看是否进行
	UPDATE t_export_order_receipt SET order_head_id = b.id 
  FROM t_export_order_receipt a LEFT JOIN t_export_order_head b 
	ON a.ebc_code+a.order_no = b.ebc_code+b.order_no AND a.remove = 0 AND B.remove = 0

END

上面的存储过程不使用临时表还有一种写法,效率较低

ALTER PROCEDURE [dbo].[proc_export_arrival]
AS
BEGIN
	DECLARE @code nvarchar(1000);
	DECLARE @return_status nvarchar(10);
	DECLARE @return_time nvarchar(17);
	DECLARE @return_info nvarchar(1000);
	DECLARE @i bigint;
	DECLARE @j bigint;
	SET @i = 1;
	SET @j = (SELECT COUNT(*) FROM (SELECT operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no) a);
	WHILE @i< = @j BEGIN
		-- 查询业务主键
		SET @code = (SELECT code FROM(
		SELECT rn=row_number() over(order by operator_code + '-' + cop_no asc) , operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no
		) a WHERE a.rn = @i);
		-- 查询最新回执时间
		SET @return_time = (SELECT return_time FROM(
		SELECT rn=row_number() over(order by operator_code + '-' + cop_no asc) , operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no
		) a WHERE a.rn = @i);
		-- 查询最新回执状态
		SET @return_status = (SELECT return_status FROM t_export_arrival_receipt WHERE operator_code + '-' + cop_no = @code AND return_time = @return_time AND remove = 0);
		-- 查询最新回执信息
		SET @return_info = (SELECT return_info FROM t_export_arrival_receipt WHERE operator_code + '-' + cop_no = @code AND return_time = @return_time AND remove = 0);
		--更新表头
		UPDATE t_export_arrival_head SET return_info = @return_info, return_time = @return_time, return_status = @return_status WHERE operator_code + '-' + cop_no = @code AND remove = 0;
		SET @i = @i+1;
	END
END

另外可以注意到使用row_number()来遍历临时表的存储过程,完全可以用update select JOIN多重子查询来代替,不再过多写了