sqlserver 递归查询

-- 构建递归结构的表（数据是虚构的）
drop table t;
create table t
(
  id int, name varchar(6), pid int
);
insert into t
select 1,'安徽',0 union all
select 2,'安庆',1 union all
select 3,'安庆市',2 union all
select 4,'怀宁县',2 union all
select 5,'潜山县',2 union all
select 6,'宿松县',2 union all
select 7,'太湖县',3 union all
select 8,'桐城市',3 union all
select 9,'望江县',4 union all
select 10,'岳西县',4 union all
select 11,'枞阳县',2
;

 

-- 递归查询某个节点的所有父节点
;with cte as
(
select * from t where id=11
union all
select t.* from t,cte where t.id=cte.pid
)
select * from cte order by id;

 

-- 递归查询整个树
;with cte(id, name, pid, lvl) as
(
 select t.*, 0 lvl from t where pid=0
 union all
 select t.*, cte.lvl+1 lvl from t, cte where t.pid = cte.id
)
select
  case
    when lvl=0 then name
    else REPLICATE('　', lvl) + '└' + name
  end,
  id,
  pid
from cte
;

但是，这个结果排序是有问题的，如太湖县应该挂在安庆县下面。如果解决这个问题？请不啻指教