8.1.3 树形数据编号重排示例.sql

sqlserver 数据库编程的绝好脚本

--测试数据
DECLARE @t TABLE(No varchar(10))
INSERT @t SELECT '1'
UNION ALL SELECT '3'
UNION ALL SELECT '302'
UNION ALL SELECT '305'
UNION ALL SELECT '305001'
UNION ALL SELECT '305005'
UNION ALL SELECT '6'
UNION ALL SELECT '601'

--重排编码
UPDATE a SET 
	No=RIGHT(--重排第一层编码
			(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1)
		+CASE --重排第二层编码
			WHEN LEN(No)>1 
			THEN RIGHT(100+
				(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+'__'),2)
			ELSE '' END
		+CASE --重排第三层编码
			WHEN LEN(No)>3
			THEN RIGHT(1000+
				(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+'___'),3)
			ELSE '' END
FROM @t a

--显示处理结果
SELECT * FROM @t
/*--结果
No         
---------- 
1
2
201
202
202001
202002
3
301
--*/