多级关联查询

Question

现在有这样一个需求，望大神帮忙解答。分不多。表的字段是 
id 地区id
name  地区名称
pid 上级地区ID
怎么查询出所有省会城市下包含的所有市，区，县
左边中只显示省。右边显示该省下面的所有，市，区，县
fatherid ----fathername  ----sonid ----sonname
1011             四川                 101111        成都市
1011            四川                   10111101    高新区       
1011            四川                   10111101    应龙县
1011             四川                 101112        南充市
1011            四川                   10111201    顺庆区       
1011            四川                   10111202    高坪区
其中右边查询的顺序可以不管

Answer 1

--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(23),[pid] int)
Insert #T
select 1011,N'四川',0 union all
select 101111,N'成都市',1011 union all
select 10111101,N'高新区',101111 union all
select 10111101,N'应龙县',101111
Go
--测试数据结束
SELECT a.id AS fatherid,
       a.name AS fathername,
       b.id AS sonid,
       b.name AS sonname
FROM #T a
    JOIN #T b
        ON a.id = LEFT(b.pid, 4)
           AND a.pid = 0;

Answer 2

不好意思。是我没有描述清楚。id是newid()生成

Answer 3

--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(23),[pid] int)
Insert #T
select 1,N'四川',0 union all
select 2,N'成都市',1 union all
select 3,N'高新区',2 union all
select 4,N'应龙县',2
Go
--测试数据结束
SELECT a.id AS fatherid,
       a.name AS fathername,
       b.id AS sonid,
       b.name AS sonname
FROM #T a
    JOIN #T b
        ON a.id = b.pid
and a.pid=0
UNION ALL
SELECT a.id AS fatherid,
       a.name AS fathername,
       c.id AS sonid,
       c.name AS sonname
FROM #T a
    JOIN #T b
        ON a.id = b.pid AND  a.pid=0
JOIN #T c ON b.id=c.pid