树形数据编号重排

--测试数据
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
--*/