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

今天,有两张表需要互相同步数据,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多重子查询来代替,不再过多写了