求个UPDATE语句，在线等

Question

select
    a.*,isnull(b.id,0) as parent
from
    表 a
left join
    表 b
on
    a.grade=b.grade+1 and a.number like b.number+'%'

Answer 1

declare @t table(id int,number varchar(12),grade int,[end] int)
insert into @t values(1,rtrim('01    '),1,0)
insert into @t values(2,rtrim('0101  '),2,1)
insert into @t values(3,rtrim('02    '),1,0)
insert into @t values(4,rtrim('0201  '),2,0)
insert into @t values(5,rtrim('020101'),3,1)select
    a.*,isnull(b.id,0) as parent
from
    @t a
left join
    @t b
on
    a.grade=b.grade+1 and a.number like b.number+'%'/*
id          number       grade       end         parent      
----------- ------------ ----------- ----------- ----------- 
1           01           1           0           0
2           0101         2           1           1
3           02           1           0           0
4           0201         2           0           3
5           020101       3           1           4
*/

Answer 2

create table tb(id int,number varchar(10),grade int,[end] int)
insert into tb values(1,         '01'    ,           1,                 0   )      
insert into tb values(2,         '0101'  ,           2,                 1   )        
insert into tb values(3,         '02'    ,           1,                 0   )        
insert into tb values(4,         '0201'  ,           2,                 0   )        
insert into tb values(5,         '020101',           3,                 1   )
goselect * , parent = 
  (case when len(t.number) = 2 and t.[end] = 0 then 0 
        else (select id from tb where number = left(t.number,len(t.number) - 2))
   end) 
from tb t
drop table tb/*
id          number     grade       end         parent      
----------- ---------- ----------- ----------- ----------- 
1           01         1           0           0
2           0101       2           1           1
3           02         1           0           0
4           0201       2           0           3
5           020101     3           1           4（所影响的行数为 5 行）
*/

Answer 3

create table tb(id int,number varchar(10),grade int,[end] int)
insert into tb values(1,         '01'    ,           1,                 0   )      
insert into tb values(2,         '0101'  ,           2,                 1   )        
insert into tb values(3,         '02'    ,           1,                 0   )        
insert into tb values(4,         '0201'  ,           2,                 0   )        
insert into tb values(5,         '020101',           3,                 1   )
go
select distinct
    a.*,isnull(b.id,0) as parent
from tb a
left join tb b
on
    a.grade=b.grade+1 and substring(a.number,1,len(b.number)) = b.numberdrop table tb
/*id          number     grade       end         parent      
----------- ---------- ----------- ----------- ----------- 
1           01         1           0           0
2           0101       2           1           1
3           02         1           0           0
4           0201       2           0           3
5           020101     3           1           4
*/