22,206
社区成员
发帖
与我相关
我的任务
分享
select id,desp,p_id=(select max(id) from tb where a.id<>id and a.id like id+'%')
from tb a
01 A NULL
0101 A1 01
010101 A11 0101
010102 A12 0101
0102 A2 01
010201 A21 0102
010202 A22 0102
02 B NULL
0201 B1 02
0202 B2 02
select id,desp,left(id,(len(desp)-1)*2) as pid from tb1
declare @T table([id] nvarchar(6),[desp] nvarchar(3))
Insert @T
select N'01',N'A' union all
select N'0101',N'A1' union all
select N'010101',N'A11' union all
select N'010102',N'A12' union all
select N'0102',N'A2' union all
select N'010201',N'A21' union all
select N'010202',N'A22' union all
select N'02',N'B' union all
select N'0201',N'B1' union all
select N'0202',N'B2'
Select
*,ParentID=(select isnull(max(ID),'') from @T where t.ID like [id]+'%' and ID<t.ID)
from
@T t
(10 個資料列受到影響)
id desp ParentID
------ ---- --------
01 A
0101 A1 01
010101 A11 0101
010102 A12 0101
0102 A2 01
010201 A21 0102
010202 A22 0102
02 B
0201 B1 02
0202 B2 02
(10 個資料列受到影響)