declare @t table(id int,name varchar(10),lastid int)
insert into @t select 100,'省1',null
insert into @t select 200,'省2',null
insert into @t select 300,'市1',100
insert into @t select 400,'市2',100
insert into @t select 500,'市3',200
insert into @t select 600,'省3',null
insert into @t select 700,'市4',600select a.id,a.name,b.name from @t a,@t b where a.id=b.lastid order by a.id/*
id name name
----------- ---------- ----------
100 省1 市1
100 省1 市2
200 省2 市3
600 省3 市4
*/
insert into @t select 100,'省1',null
insert into @t select 200,'省2',null
insert into @t select 300,'市1',100
insert into @t select 400,'市2',100
insert into @t select 500,'市3',200
insert into @t select 600,'省3',null
insert into @t select 700,'市4',600select a.id,a.name,b.name from @t a,@t b where a.id=b.lastid order by a.id/*
id name name
----------- ---------- ----------
100 省1 市1
100 省1 市2
200 省2 市3
600 省3 市4
*/
insert into @t select 100,'省1',null
insert into @t select 200,'省2',null
insert into @t select 300,'市1',100
insert into @t select 400,'市2',100
insert into @t select 500,'市3',200
insert into @t select 600,'省3',null
insert into @t select 700,'市4',600select
case when exists(select 1 from @t where id>b.id and lastid=b.lastid) then '' else a.id end,
case when exists(select 1 from @t where id>b.id and lastid=b.lastid) then '' else a.name end,
b.name
from @t a,@t b where a.id=b.lastid order by a.id,b.id
/*
name
---------- ---------- ----------
市1
100 省1 市2
200 省2 市3
600 省3 市4
*/
100 省1 null
300 市1 100
400 市2 100
200 省2 null
500 市3 200
600 省3 null
700 市4 600
insert into @t select 100,'省1',null
insert into @t select 200,'省2',null
insert into @t select 300,'市1',100
insert into @t select 400,'市2',100
insert into @t select 500,'市3',200
insert into @t select 600,'省3',null
insert into @t select 700,'市4',600select * from @t order by isnull(lastid,id)/*
id name lastid
----------- ---------- -----------
100 省1 NULL
300 市1 100
400 市2 100
200 省2 NULL
500 市3 200
600 省3 NULL
700 市4 600
*/