SQL查询下级所有子分类（例如查询省下面所有的市，区）

RW~

已于 2022-03-10 11:16:33 修改

阅读量5.2k

点赞数 1

分类专栏： Sql Server 文章标签： sql dba 数据库

于 2019-04-07 12:02:51 首次发布

本文链接：https://blog.csdn.net/weixin_43497935/article/details/89067511

版权

Sql Server 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

方式一

with a as(
select ProjectTreeCode,ProjectTreeName,UpCode,0 AS level from LNGT_BIM_ProjectTree where ProjectTreeCode='Q01'
union all
select x.ProjectTreeCode,x.ProjectTreeName,a.UpCode,level+1 from LNGT_BIM_ProjectTree x,a
where x.UpCode = a.ProjectTreeCode)
select * from a

方式二

with a as(
WITH cte AS (
SELECT
 LEVEL = 1,
 Level1 = cast( ROW_NUMBER ( ) over ( ORDER BY OrderID ) AS VARCHAR ( 50 ) ) 
FROM
 ZSJT_BIMCD_Component 
UNION ALL
SELECT
 LEVEL = b.LEVEL + 1,
 Level1 = cast(
 ( b.Level1 + '-' + CONVERT ( VARCHAR ( 50 ), ROW_NUMBER ( ) over ( ORDER BY a.OrderID ) ) ) AS VARCHAR ( 50 ) 
 ) 
FROM
 ZSJT_BIMCD_Component a
 INNER JOIN cte b ON a.ParentPartNumber = b.PartNumber 
 ) 
SELECT LEVEL FROM cte ORDER BY Level1