多级关联查询

[zgluren]

现在有这样一个需求，望大神帮忙解答。分不多。

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

[二月十六]

--测试数据
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;

[zgluren]

引用 1 楼 sinat_28984567 的回复:

--测试数据
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;

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

[二月十六]

--测试数据
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