create table T1(this varchar(10), parent varchar(10))

insert into T1(this,parent)

values ("id1",null)

,("id2",null)

,("id3","id1")

,("id4","id2")

,("id5","id3")

,("id6","id3")

,("id7","id4")

,("id8","id7")

-- sql server的cte功能

with tree(this,parent,root,depth) as (

select this,parent, this as root, 1 as depth from T1 where parent is null

union all

select a.this,a.parent, b.root, b.depth+1 as depth from T1 a, tree b where a.parent=b.this

)

select this,parent,root,depth

from tree

order by root,depth,this