SqlServer实现递归查询

在一般的系统开发中，我们经常遇到一类问题：查询出某条记录以及与该条记录相关的其他记录。例如，现在需要查询出西湖区以及西湖区所属的市和省，这时候就需要用到SQL递归查询。我在这里构造了一张数据表[tb_Test]，其数据如下所示：

Id	 Name	ParentId
1	浙江省	  NULL
2	杭州市	   1
3	湖州市	   1
4	滨江区	   2
5	拱墅区	   2
6	西湖区	   2
7	吴兴区	   3
8	南浔区	   3
9	长兴县	   3

向下递归

假设我们现在需要查询杭州市及其下属的区县，其代码如下所示：

with cte as 
(
   select Id,Name,ParentId from [tb_Test] where Name='杭州市'
   union all
   select a.Id,a.Name,a.ParentId from [tb_Test] a inner join cte on a.ParentId=cte.Id
) select * from cte

结果如下所示：

Id	 Name	ParentId
2	杭州市	   1
4	滨江区	   2
5	拱墅区	   2
6	西湖区	   2

向上递归

假设现在需要查询西湖区及其所属的市和省，其代码如下所示：

with cte as 
(
   select Id,Name,ParentId from [tb_Test] where Name='西湖区'
   union all
   select a.Id,a.Name,a.ParentId from [tb_Test] a inner join cte on a.Id=cte.ParentId
) select * from cte

结果如下所示：

Id	 Name	ParentId
6	西湖区	   2
2	杭州市	   1
1	浙江省	  NULL

递归分页

很多时候我们需要分页查询数据，递归分页查询的代码如下所示：

with cte as 
(
   select Id,Name,ParentId from [tb_Test] where Name='杭州市'
   union all
   select a.Id,a.Name,a.ParentId from [tb_Test] a inner join cte on a.ParentId=cte.Id
) select * from(select ROW_NUMBER() over(order by Id) as rid,* from cte) b where b.rid between 1 and 2

结果如下所示：

rid  Id	 Name	ParentId
1    2	杭州市	   1
2    4	滨江区	   2