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

很好的sql 文档,可能邦你成为sql 高手

--测试资料
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
--*/