TSql 分层和递归查询

时间：2022-03-14 10:48

1，需求分析

在TSql中实现层次结构，例如有这样一种数据结构，省，市，县，乡，村，如何使用一张表表示这种数据结构，并且允许是不对称的，例如，上海市是个直辖市，没有省份。

create table dbo.hierarchy
(
ID  int not null primary key,
--type int not null,
ParentID int not null,
name varchar(100) not null
)

type表示类型，可以设置：省，Type是1；市，type是2，以此类推。

ParentID标识的是父级ID，例如信阳市的ParentID是河南省的ID。

2，插入测试数据

测试数据格式说明了归属关系，博主懒，去掉type字段。

insert into dbo.hierarchy
values(1,0,‘河南省‘)
,(2,1,‘信阳市‘),(3,2,‘淮滨县‘),(4,3,‘芦集乡‘),(12,3,‘邓湾乡‘),(13,3,‘台头乡‘),(14,3,‘谷堆乡‘)
              ,(8,2,‘固始县‘),(9,8,‘李店乡‘)
              ,(10,2,‘息县‘),(11,10,‘关店乡‘)
,(5,1,‘安阳市‘),(6,5,‘滑县‘),(7,6,‘老庙乡‘)
,(15,1,‘南阳市‘),(16,15,‘方城县‘)
,(17,1,‘驻马店市‘),(18,17,‘正阳县‘)


select * 
from dbo.hierarchy 
order by ParentID

3，递归查询

由于实际的数据可能有很多，所以，要想获取河南省下的所有市，县，乡，村等信息，必须使用递归查询，如何编写递归查询？博主对递归查询不是很熟，但是有简单的认识

1，初始条件
2，调用自身
3，终止条件

4，在TSQL中，CTE能够很好的支持递归查询，递归查询不支持子查询

;with cte as
(
select * 
from dbo.hierarchy 
where id=1

union all

select * 
from dbo.hierarchy 
where ParentID in(select id from cte)
    and id not in(select id from cte)
)
select *
from cte

Sql Server 报错

Msg 465, Level 16, State 1, Line 11
Recursive references are not allowed in subqueries.

5，将子查询修改为inner join

;with cte(Id,ParentID,Name) as
(
select * 
from dbo.hierarchy 
where id=1

union all

select h.* 
from dbo.hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

如果要查看向内递归到多少level，可以使用派生列，level=0是省level，level=1是市level，依次类推。

;with cte(Id,ParentID,Name,Level) as
(
select ID,ParentID,Name,0 as Level
from dbo.hierarchy 
where id=1

union all

select h.ID,h.ParentID,h.Name,c.Level+1 as Level
from dbo.hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

技术分享