5.3.3 使用临时表进行编号重排的处理示例.sql

来自「sqlserver 数据库编程的绝好脚本」· SQL 代码 · 共 35 行

--测试资料
CREATE TABLE tb(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2))
INSERT tb SELECT 'aa','0001',1
UNION ALL SELECT 'aa','0003',2
UNION ALL SELECT 'aa','0004',3
UNION ALL SELECT 'bb','0005',4
UNION ALL SELECT 'bb','0006',5
UNION ALL SELECT 'cc','0007',6
UNION ALL SELECT 'cc','0009',7
GO

--重排编号处理
SELECT ID=IDENTITY(int,0,1),* INTO # FROM tb ORDER BY ID1,ID2
UPDATE a SET ID2=RIGHT(10001+b1.ID-b2.ID,4)
FROM tb a,# b1,(SELECT ID1,ID=MIN(ID) FROM # GROUP BY ID1)b2
WHERE a.ID1=b1.ID1 AND a.ID2=b1.ID2
	AND b1.ID1=b2.ID1
DROP TABLE #
SELECT * FROM tb
/*--结果
ID1  ID2  col
---- ---- ----------- 
aa   0001 1
aa   0002 2
aa   0003 3
bb   0001 4
bb   0002 5
cc   0001 6
cc   0002 7
--*/