?? 8.1.3 樹形數據編號重排示例.sql
字號:
--測試數據
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
--*/
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -