SQL多级树形查询（CTE）

墨蓝 2023-02-15 13:46 8阅读 0赞

表 POI 有 字段 Title CityID

表City 有字段 ID Title ParentCityID（归属City）

求当 烟台下的所有PoiTitle (包含烟台下的子城市中的PoiTitle)

第一步先查询 烟台下所有城市（PCityId指向烟台） CTE

with cte\_child(id,title,ParentCityId) as (select id,title,ParentCityId from CMSCity where Title=‘烟台’ union all select a.id,a.title,a.ParentCityId from CMSCity a  
inner join cte\_child b on ( a.ParentCityId=b.id)) select \* from cte\_child  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA_size_16_color_FFFFFF_t_70]  
第二步操作 poi  
修改后sql为：  
with cte\_child(id,title,ParentCityId) as (select id,title,ParentCityId from CMSCity where Title=‘烟台’ union all select a.id,a.title,a.ParentCityId from CMSCity a  
inner join cte\_child b on ( a.ParentCityId=b.id)) select CMSPois.CityId,CMSPois.Title from CMSPois inner join cte\_child on CMSPois.CityId =cte\_child.id  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200605191717775.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200605191819421.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTI4Njc0NA==,size_16,color_FFFFFF,t_70