父子关系结构更新

Question

刚没描述清楚，现重新发了一贴，望大侠帮忙再看看。根据ProductCode这列的父子关系，更新ParentID和ID的父子关系。谢谢
1 NULL 01 农业产品 NULL
2 NULL 0111 谷物 NULL
3 NULL 011110 稻谷 千克
4 NULL 01111011 籼稻 千克
5 NULL 0111101101 种用籼稻 千克
6 NULL 0111101199 其他籼稻 千克
7 NULL 01111021 粳稻 千克
8 NULL 0111102101 种用粳稻 千克
9 NULL 0111102199 其他粳稻 千克
10 NULL 01111051 糯稻 千克
11 NULL 0111105101 种用糯稻 千克
12 NULL 0111105199 其他糯稻 千克
13 NULL 01111090 其他稻谷 千克
14 NULL 0111109001 其他种用稻谷 千克
15 NULL 0111109099 未列明的其他稻谷 千克
16 NULL 011120 小麦 千克
17 NULL 01112011 硬粒小麦 千克
18 NULL 0111201101 种用硬粒小麦 千克
19 NULL 0111201199 其他硬粒小麦 千克
20 NULL 01112021 软粒小麦 千克
21 NULL 0111202101 种用软粒小麦 千克
22 NULL 0111202199 其他软粒小麦 千克
23 NULL 01112051 混合小麦 千克
24 NULL 0111205101 种用混合小麦 千克
25 NULL 0111205199 其他混合小麦 千克
26 NULL 01112090 其他小麦 千克
27 NULL 0111209001 种用其他小麦 千克
28 NULL 0111209099 未列明的其他小麦 千克
29 NULL 011140 玉米 千克
30 NULL 01114011 黄玉米 千克
31 NULL 0111401101 种用黄玉米 千克
32 NULL 0111401199 其他黄玉米 千克
33 NULL 01114021 白玉米 千克
34 NULL 0111402101 种用白玉米 千克
35 NULL 0111402199 其他白玉米 千克
36 NULL 01114051 混合玉米 千克
37 NULL 0111405101 种用混合玉米 千克
38 NULL 0111405199 其他混合玉米 千克
39 NULL 01114090 其他玉米 千克
40 NULL 0111409001 种用其他玉米 千克

Answer 1

 
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID INT,ParentID INT,ProductCode VARCHAR(10),Name VARCHAR(16),Units VARCHAR(4))
INSERT INTO @tb
SELECT 1,null,'01','农业产品',null UNION ALL
SELECT 2,null,'0111','谷物',null UNION ALL
SELECT 3,null,'011110','稻谷','千克' UNION ALL
SELECT 4,null,'01111011','籼稻','千克' UNION ALL
SELECT 5,null,'0111101101','种用籼稻','千克' UNION ALL
SELECT 6,null,'0111101199','其他籼稻','千克' UNION ALL
SELECT 7,null,'01111021','粳稻','千克' UNION ALL
SELECT 8,null,'0111102101','种用粳稻','千克' UNION ALL
SELECT 9,null,'0111102199','其他粳稻','千克' UNION ALL
SELECT 10,null,'01111051','糯稻','千克' UNION ALL
SELECT 11,null,'0111105101','种用糯稻','千克' UNION ALL
SELECT 12,null,'0111105199','其他糯稻','千克' UNION ALL
SELECT 13,null,'01111090','其他稻谷','千克' UNION ALL
SELECT 14,null,'0111109001','其他种用稻谷','千克' UNION ALL
SELECT 15,null,'0111109099','未列明的其他稻谷','千克' UNION ALL
SELECT 16,null,'011120','小麦','千克' UNION ALL
SELECT 17,null,'01112011','硬粒小麦','千克' UNION ALL
SELECT 18,null,'0111201101','种用硬粒小麦','千克' UNION ALL
SELECT 19,null,'0111201199','其他硬粒小麦','千克' UNION ALL
SELECT 20,null,'01112021','软粒小麦','千克' UNION ALL
SELECT 21,null,'0111202101','种用软粒小麦','千克' UNION ALL
SELECT 22,null,'0111202199','其他软粒小麦','千克' UNION ALL
SELECT 23,null,'01112051','混合小麦','千克' UNION ALL
SELECT 24,null,'0111205101','种用混合小麦','千克' UNION ALL
SELECT 25,null,'0111205199','其他混合小麦','千克' UNION ALL
SELECT 26,null,'01112090','其他小麦','千克' UNION ALL
SELECT 27,null,'0111209001','种用其他小麦','千克' UNION ALL
SELECT 28,null,'0111209099','未列明的其他小麦','千克' UNION ALL
SELECT 29,null,'011140','玉米','千克' UNION ALL
SELECT 30,null,'01114011','黄玉米','千克' UNION ALL
SELECT 31,null,'0111401101','种用黄玉米','千克' UNION ALL
SELECT 32,null,'0111401199','其他黄玉米','千克' UNION ALL
SELECT 33,null,'01114021','白玉米','千克' UNION ALL
SELECT 34,null,'0111402101','种用白玉米','千克' UNION ALL
SELECT 35,null,'0111402199','其他白玉米','千克' UNION ALL
SELECT 36,null,'01114051','混合玉米','千克' UNION ALL
SELECT 37,null,'0111405101','种用混合玉米','千克' UNION ALL
SELECT 38,null,'0111405199','其他混合玉米','千克' UNION ALL
SELECT 39,null,'01114090','其他玉米','千克' UNION ALL
SELECT 40,null,'0111409001','种用其他玉米','千克'--SQL查询如下:UPDATE A SET
    ParentId = (SELECT MAX(ID) FROM @tb 
                WHERE A.ProductCode LIKE ProductCode + '_%')
FROM @tb AS A;SELECT * FROM @tb;/*
ID          ParentID    ProductCode Name             Units
----------- ----------- ----------- ---------------- -----
1           NULL        01          农业产品             NULL
2           1           0111        谷物               NULL
3           2           011110      稻谷               千克
4           3           01111011    籼稻               千克
5           4           0111101101  种用籼稻             千克
6           4           0111101199  其他籼稻             千克
7           3           01111021    粳稻               千克
8           7           0111102101  种用粳稻             千克
9           7           0111102199  其他粳稻             千克
10          3           01111051    糯稻               千克
11          10          0111105101  种用糯稻             千克
12          10          0111105199  其他糯稻             千克
13          3           01111090    其他稻谷             千克
14          13          0111109001  其他种用稻谷           千克
15          13          0111109099  未列明的其他稻谷         千克
16          2           011120      小麦               千克
17          16          01112011    硬粒小麦             千克
18          17          0111201101  种用硬粒小麦           千克
19          17          0111201199  其他硬粒小麦           千克
20          16          01112021    软粒小麦             千克
21          20          0111202101  种用软粒小麦           千克
22          20          0111202199  其他软粒小麦           千克
23          16          01112051    混合小麦             千克
24          23          0111205101  种用混合小麦           千克
25          23          0111205199  其他混合小麦           千克
26          16          01112090    其他小麦             千克
27          26          0111209001  种用其他小麦           千克
28          26          0111209099  未列明的其他小麦         千克
29          2           011140      玉米               千克
30          29          01114011    黄玉米              千克
31          30          0111401101  种用黄玉米            千克
32          30          0111401199  其他黄玉米            千克
33          29          01114021    白玉米              千克
34          33          0111402101  种用白玉米            千克
35          33          0111402199  其他白玉米            千克
36          29          01114051    混合玉米             千克
37          36          0111405101  种用混合玉米           千克
38          36          0111405199  其他混合玉米           千克
39          29          01114090    其他玉米             千克
40          39          0111409001  种用其他玉米           千克(40 行受影响)
*/

Answer 2

DECLARE @TB TABLE([ID] INT, [ParentID] INT, [ProductCode] VARCHAR(10), [Name] NVARCHAR(8), [Units] NVARCHAR(2))
INSERT @TB 
SELECT 2, NULL, '0111', N'谷物', NULL UNION ALL 
SELECT 3, NULL, '011110', N'稻谷', N'千克' UNION ALL 
SELECT 4, NULL, '01111011', N'籼稻', N'千克' UNION ALL 
SELECT 5, NULL, '0111101101', N'种用籼稻', N'千克' UNION ALL 
SELECT 6, NULL, '0111101199', N'其他籼稻', N'千克' UNION ALL 
SELECT 7, NULL, '01111021', N'粳稻', N'千克' UNION ALL 
SELECT 8, NULL, '0111102101', N'种用粳稻', N'千克' UNION ALL 
SELECT 9, NULL, '0111102199', N'其他粳稻', N'千克' UNION ALL 
SELECT 10, NULL, '01111051', N'糯稻', N'千克' UNION ALL 
SELECT 11, NULL, '0111105101', N'种用糯稻', N'千克' UNION ALL 
SELECT 12, NULL, '0111105199', N'其他糯稻', N'千克' UNION ALL 
SELECT 13, NULL, '01111090', N'其他稻谷', N'千克' UNION ALL 
SELECT 14, NULL, '0111109001', N'其他种用稻谷', N'千克' UNION ALL 
SELECT 15, NULL, '0111109099', N'未列明的其他稻谷', N'千克' UNION ALL 
SELECT 16, NULL, '011120', N'小麦', N'千克' UNION ALL 
SELECT 17, NULL, '01112011', N'硬粒小麦', N'千克' UNION ALL 
SELECT 18, NULL, '0111201101', N'种用硬粒小麦', N'千克' UNION ALL 
SELECT 19, NULL, '0111201199', N'其他硬粒小麦', N'千克' UNION ALL 
SELECT 20, NULL, '01112021', N'软粒小麦', N'千克' UNION ALL 
SELECT 21, NULL, '0111202101', N'种用软粒小麦', N'千克' UNION ALL 
SELECT 22, NULL, '0111202199', N'其他软粒小麦', N'千克' UNION ALL 
SELECT 23, NULL, '01112051', N'混合小麦', N'千克' UNION ALL 
SELECT 24, NULL, '0111205101', N'种用混合小麦', N'千克' UNION ALL 
SELECT 25, NULL, '0111205199', N'其他混合小麦', N'千克' UNION ALL 
SELECT 26, NULL, '01112090', N'其他小麦', N'千克' UNION ALL 
SELECT 27, NULL, '0111209001', N'种用其他小麦', N'千克' UNION ALL 
SELECT 28, NULL, '0111209099', N'未列明的其他小麦', N'千克' UNION ALL 
SELECT 29, NULL, '011140', N'玉米', N'千克' UNION ALL 
SELECT 30, NULL, '01114011', N'黄玉米', N'千克' UNION ALL 
SELECT 31, NULL, '0111401101', N'种用黄玉米', N'千克' UNION ALL 
SELECT 32, NULL, '0111401199', N'其他黄玉米', N'千克' UNION ALL 
SELECT 33, NULL, '01114021', N'白玉米', N'千克' UNION ALL 
SELECT 34, NULL, '0111402101', N'种用白玉米', N'千克' UNION ALL 
SELECT 35, NULL, '0111402199', N'其他白玉米', N'千克' UNION ALL 
SELECT 36, NULL, '01114051', N'混合玉米', N'千克' UNION ALL 
SELECT 37, NULL, '0111405101', N'种用混合玉米', N'千克' UNION ALL 
SELECT 38, NULL, '0111405199', N'其他混合玉米', N'千克' UNION ALL 
SELECT 39, NULL, '01114090', N'其他玉米', N'千克' UNION ALL 
SELECT 40, NULL, '0111409001', N'种用其他玉米', N'千克'
UPDATE @TB SET ParentID=0 
WHERE Units IS NULL AND LEN(ProductCode)=4WHILE @@ROWCOUNT>0
BEGIN
    UPDATE A SET ParentID=B.ID
    FROM @TB AS A, @TB AS B
    WHERE  LEN(A.ProductCode)=LEN(B.ProductCode)+2 
           AND A.ProductCode LIKE B.ProductCode+'%'
           AND A.ParentID IS NULL
           AND B.ParentID IS NOT NULL
ENDSELECT *
FROM @TB 
/*ID          ParentID    ProductCode Name     Units 
----------- ----------- ----------- -------- ----- 
2           0           0111        谷物       NULL
3           2           011110      稻谷       千克
4           3           01111011    籼稻       千克
5           4           0111101101  种用籼稻     千克
6           4           0111101199  其他籼稻     千克
7           3           01111021    粳稻       千克
8           7           0111102101  种用粳稻     千克
9           7           0111102199  其他粳稻     千克
10          3           01111051    糯稻       千克
11          10          0111105101  种用糯稻     千克
12          10          0111105199  其他糯稻     千克
13          3           01111090    其他稻谷     千克
14          13          0111109001  其他种用稻谷   千克
15          13          0111109099  未列明的其他稻谷 千克
16          2           011120      小麦       千克
17          16          01112011    硬粒小麦     千克
18          17          0111201101  种用硬粒小麦   千克
19          17          0111201199  其他硬粒小麦   千克
20          16          01112021    软粒小麦     千克
21          20          0111202101  种用软粒小麦   千克
22          20          0111202199  其他软粒小麦   千克
23          16          01112051    混合小麦     千克
24          23          0111205101  种用混合小麦   千克
25          23          0111205199  其他混合小麦   千克
26          16          01112090    其他小麦     千克
27          26          0111209001  种用其他小麦   千克
28          26          0111209099  未列明的其他小麦 千克
29          2           011140      玉米       千克
30          29          01114011    黄玉米      千克
31          30          0111401101  种用黄玉米    千克
32          30          0111401199  其他黄玉米    千克
33          29          01114021    白玉米      千克
34          33          0111402101  种用白玉米    千克
35          33          0111402199  其他白玉米    千克
36          29          01114051    混合玉米     千克
37          36          0111405101  种用混合玉米   千克
38          36          0111405199  其他混合玉米   千克
39          29          01114090    其他玉米     千克
40          39          0111409001  种用其他玉米   千克(39 row(s) affected)
*/

Answer 3

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