create table question(
id int,
t_id varchar(40),
sup_id int
)
insert into question
select 1,'1',1 union all
select 2,'1.1',2 union all
select 3,'1.1.1',3 union all
select 4,'1.1.1.1',4 union all
select 5,'2',1 union all
select 6,'2.1',2
go

update a
set a.t_id = b.id
from question a join question b
    on right(reverse(a.t_id),len(reverse(a.t_id))-charindex('.',reverse(a.t_id)))
        = reverse(b.t_id)

select * from question

drop table question


/************************

id          t_id                                     sup_id
----------- ---------------------------------------- -----------
1           1                                        1
2           1                                        2
3           2                                        3
4           3                                        4
5           5                                        1
6           5                                        2

(6 行受影响)

------解决方案--------------------
SQL code

--id t_id sup_id
--------------------------
--1 | 1 | 1
--2 | 1.1 | 2
--3 | 1.1.1 | 3
--4 | 1.1.1.1 | 4
--5 | 2 | 1
--6 | 2.1 | 2
------------------------------

DECLARE @tab TABLE (id INT, t_id VARCHAR(20), sup_id INT)
INSERT INTO @tab
SELECT 1,'1',1 UNION ALL
SELECT 2,  '1.1' , 2 UNION ALL
SELECT 3, '1.1.1' , 3 UNION ALL
SELECT 4,  '1.1.1.1',  4 UNION ALL
SELECT 5, '2',  1 UNION ALL
SELECT 6, '2.1',  2

;WITH cte1 AS 
(
    SELECT *,
    CASE WHEN LEN(t_id)>=3 THEN SUBSTRING(t_id,1,LEN(t_id)-2) ELSE t_id END AS a
    FROM @tab
) ,cte2 AS 
(    
    SELECT *,parentId=1 FROM cte1 WHERE LEN(t_id)=1
    UNION ALL
    SELECT *,
    parentId=(SELECT id FROM cte1 WHERE t_id=t1.a ) 
    FROM cte1 t1 WHERE LEN(t1.t_id)>1
)
SELECT id,t_id,sup_id=parentId FROM cte2 ORDER BY id

/*
id          t_id                 sup_id
----------- -------------------- -----------
1           1                    1
2           1.1                  1
3           1.1.1                2
4           1.1.1.1              3
5           2                    1
6           2.1                  5

(6 行受影响)
*/

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'question')
BEGIN
    DROP TABLE question
END
GO
create table question(
id int,
t_id varchar(40),
sup_id int
)
insert into question
select 1,'1',1 union all
select 2,'1.1',2 union all
select 3,'1.1.1',3 union all
select 4,'1.1.1.1',4 union all
select 5,'2',1 union all
select 6,'2.1',2
go

UPDATE B SET B.sup_id = A.ID FROM question AS B, question AS A WHERE LEFT(B.t_Id,LEN(B.t_id) - CHARINDEX('.',reverse(B.t_id))) = A.t_id



SELECT * FROM question

id    t_id    sup_id
1    1    1
2    1.1    1
3    1.1.1    2
4    1.1.1.1    3
5    2    5
6    2.1    5