关于树形结构的查询——使用with cet递归（2005及以上版本）

对象分类表结构：
ID
Name
P_id （父ID）

示例数据：
ID Name P_id
1 交通 0
2 地铁 1
3 公交 1
4 地铁1号站 2
5 23路 3

注：有多少层子节点未定，有可能23路下面还有具体的站点

对象表：
ID
对象分类ID
坐标

示例数据：
ID 对象分类ID 坐标
1 4 （40，100）
2 5 （ 10，20 ）

业务说明：对象表做地图标准，记录标注信息，具体到分类表的最后一层，比如：23路，地铁1号线
目标：通过交通，查询出所有的对象信息（比如在C#中TreeView点击交通，对象列表中显示出所有对应此分类的对象），不使用存储过程或者函数，只用一条SQL

 

if object_id('tb') is not null drop table tb Go Create table tb(ID int,[Name] varchar(10),P_id int) Insert tb select 1 ,'交通', 0 union all select 2 ,'地铁', 1 union all select 3 ,'公交', 1 union all select 4 ,'地铁1号站', 2 union all select 5 ,'23路', 3 union all select 6 ,'531路', 3 union all select 7 ,'部门',0 union all select 8 ,'业务部',7 Go if object_id('obj') is not null drop table obj Go Create table obj(ID int,tb_id int,Name varchar(10)) Insert obj select 1,4 ,'地铁1号站' union all select 2,5 ,'23路' union all select 3,6 ,'531路' union all select 4,8 ,'业务部' Go with t1 as ( select * from tb where ID=7 --这个根据但前id查询数据 union递归其所有子数据 union all select a.* from tb a,t1 b where a.p_id=b.id ) select * from t1 inner join obj on t1.ID = obj.tb_id