关于树形结构的查询——使用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 tbGoCreate table tb(ID int,[Name] varchar(10),P_id int)Insert tbselect 1 ,'交通', 0 union allselect 2 ,'地铁', 1 union allselect 3 ,'公交', 1 union allselect 4 ,'地铁1号站', 2 union allselect 5 ,'23路', 3 union allselect 6 ,'531路', 3 union allselect 7 ,'部门',0 union allselect 8 ,'业务部',7Goif object_id('obj') is not null drop table objGoCreate table obj(ID int,tb_id int,Name varchar(10))Insert objselect 1,4 ,'地铁1号站' union allselect 2,5 ,'23路' union allselect 3,6 ,'531路' union allselect 4,8 ,'业务部'Gowith t1 as(select * from tb where ID=7 --这个根据但前id查询数据 union递归其所有子数据union allselect a.* from tb a,t1 b where a.p_id=b.id)select * from t1 inner join obj on t1.ID = obj.tb_id