sqlserver实现树形结构递归查询(无限极分类)的方法

SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式

百度百科

公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存。可以使用CTE来执行递归操作。创建的语法是:

with <name of you cte>(<column names>)
as(
<actual query>
)
select * from <name of your cte>

1、生成数据

--菜单目录结构表
create table tb_menu(
 id int not null, --主键id
title varchar(50), --标题
 parent int --parent id
);
--父菜单
insert into tb_menu(id, title, parent) values(1, '父菜单1',null);
insert into tb_menu(id, title, parent) values(2, '父菜单2',null);
insert into tb_menu(id, title, parent) values(3, '父菜单3',null);
insert into tb_menu(id, title, parent) values(4, '父菜单4',null);
insert into tb_menu(id, title, parent) values(5, '父菜单5',null);
--一级菜单
insert into tb_menu(id, title, parent) values(6, '一级菜单6',1);
insert into tb_menu(id, title, parent) values(7, '一级菜单7',1);
insert into tb_menu(id, title, parent) values(8, '一级菜单8',1);
insert into tb_menu(id, title, parent) values(9, '一级菜单9',2);
insert into tb_menu(id, title, parent) values(10, '一级菜单10',2);
insert into tb_menu(id, title, parent) values(11, '一级菜单11',2);
insert into tb_menu(id, title, parent) values(12, '一级菜单12',3);
insert into tb_menu(id, title, parent) values(13, '一级菜单13',3);
insert into tb_menu(id, title, parent) values(14, '一级菜单14',3);
insert into tb_menu(id, title, parent) values(15, '一级菜单15',4);
insert into tb_menu(id, title, parent) values(16, '一级菜单16',4);
insert into tb_menu(id, title, parent) values(17, '一级菜单17',4);
insert into tb_menu(id, title, parent) values(18, '一级菜单18',5);
insert into tb_menu(id, title, parent) values(19, '一级菜单19',5);
insert into tb_menu(id, title, parent) values(20, '一级菜单20',5);
--二级菜单
insert into tb_menu(id, title, parent) values(21, '二级菜单21',6);
insert into tb_menu(id, title, parent) values(22, '二级菜单22',6);
insert into tb_menu(id, title, parent) values(23, '二级菜单23',7);
insert into tb_menu(id, title, parent) values(24, '二级菜单24',7);
insert into tb_menu(id, title, parent) values(25, '二级菜单25',8);
insert into tb_menu(id, title, parent) values(26, '二级菜单26',9);
insert into tb_menu(id, title, parent) values(27, '二级菜单27',10);
insert into tb_menu(id, title, parent) values(28, '二级菜单28',11);
insert into tb_menu(id, title, parent) values(29, '二级菜单29',12);
insert into tb_menu(id, title, parent) values(30, '二级菜单30',13);
insert into tb_menu(id, title, parent) values(31, '二级菜单31',14);
insert into tb_menu(id, title, parent) values(32, '二级菜单32',15);
insert into tb_menu(id, title, parent) values(33, '二级菜单33',16);
insert into tb_menu(id, title, parent) values(34, '二级菜单34',17);
insert into tb_menu(id, title, parent) values(35, '二级菜单35',18);
insert into tb_menu(id, title, parent) values(36, '二级菜单36',19);
insert into tb_menu(id, title, parent) values(37, '二级菜单37',20); 

--三级菜单
insert into tb_menu(id, title, parent) values(38, '三级菜单38',21);
insert into tb_menu(id, title, parent) values(39, '三级菜单39',22);
insert into tb_menu(id, title, parent) values(40, '三级菜单40',23);
insert into tb_menu(id, title, parent) values(41, '三级菜单41',24);
insert into tb_menu(id, title, parent) values(42, '三级菜单42',25);
insert into tb_menu(id, title, parent) values(43, '三级菜单43',26);
insert into tb_menu(id, title, parent) values(44, '三级菜单44',27);
insert into tb_menu(id, title, parent) values(45, '三级菜单45',28);
insert into tb_menu(id, title, parent) values(46, '三级菜单46',28);
insert into tb_menu(id, title, parent) values(47, '三级菜单47',29);
insert into tb_menu(id, title, parent) values(48, '三级菜单48',30);
insert into tb_menu(id, title, parent) values(49, '三级菜单49',31);
insert into tb_menu(id, title, parent) values(50, '三级菜单50',31);
commit; 

2.查找所有上级节点

--查询树状结构某节点(44)的上级所有根节点 

with cte_parent(id,title,parent) 

as 

( 

  --起始条件 

  select id,title,parent 

  from tb_menu 

  where id = 44  --列出子节点查询条件 

  union all 

  --递归条件 

  select a.id,a.title,a.parent 

  from tb_menu a 

  inner join  

  cte_parent b     --执行递归,这里就要理解下了  

  on a.id=b.parent  

)             

select * from cte_parent; 

3.查找下级节点带level

--查询树状结构某节点下的所有子节点( 

with cte_child(id,title,parent,level) 

as 

( 

  --起始条件 

  select id,title,parent,0 as level 

  from tb_menu 

  where id = 6--列出父节点查询条件 

  union all 

  --递归条件 

  select a.id,a.title,a.parent,b.level+1 

  from tb_menu a 

  inner join  

  cte_child b 

  on ( a.parent=b.id)  

) 

select * from cte_child; 

以上这篇sqlserver实现树形结构递归查询(无限极分类)的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

时间: 2017-07-02

sql server实现递归查询的方法示例

本文实例讲述了sql server实现递归查询的方法示例.分享给大家供大家参考,具体如下: 有时候面对树结构的数据时需要进行递归查询,网上找了一番,参考了各位大神的文章,发现蛮简单的,当做个小笔记方便以后使用 sql server 通过CTE来支持递归查询,这对查询树形或层次结构的数据很有用 一般的树形表结构如下,相信大家都很熟悉的 id title pid 1 1级节点 0 2 2级节点 1 3 3级节点 2 4 4级节点 3 5 5级节点 4 下面上代码 ----------sql serv

SQL Server 常用函数使用方法小结

之前就想要把一些 SQL 的常用函数记录下来,不过一直没有实行...嘿嘿... 直到今天用到substring()这个函数,C# 里面这个方法起始值是 0,而 SQL 里面起始值是 1.傻傻分不清楚... 这篇博客作为记录 SQL 的函数的使用方法,想到哪里用到哪里就写到哪里... SubString():用于截取指定字符串的方法.该方法有三个参数: 参数1:用于指定要操作的字符串. 参数2:用于指定要截取的字符串的起始位置,起始值为 1 . 参数3:用于指定要截取的长度. select sub

彻底删除SQL Server注册表的方法

一般在卸载完数据库时,大家都希望能够将注册表信息完全删干净,下面就将教您彻底删除SQL Server注册表的方法,供您参考. 在卸载SQL Server开始--运行:输入regedit 进入注册表编辑器,进入之后执行下列操作: 1.彻底删除SQL Server: hkey_local_machine\software\Microsoft\MSSQLServer hkey_local_machine\software\Microsoft\Microsoft SQL Server hkey_curr

php使用sql server验证连接数据库的方法

本文实例讲述了php使用sql server验证连接数据库的方法.分享给大家供大家参考.具体分析如下: 当您连接到 SQL Server 时,SQL Server Driver for PHP 支持 SQL Server 身份验证,在使用 SQL Server 身份验证连接到 SQL Server 时必须考虑以下几点. 必须对服务器启用 SQL Server 混合模式身份验证,在尝试建立连接时必须设置 UID 和 PWD 连接属性,UID 和 PWD 必须映射到有效的 SQL Server 用户

C#获取所有SQL Server数据库名称的方法

本文实例讲述了C#获取所有SQL Server数据库名称的方法.分享给大家供大家参考.具体如下: 连接到master库中,执行 select name from master..sysdatabases . sp_helpdb也可以列出数据名,但不是全部,sp_helpdb是会根据当前用户角色找出相应的数据库,而用sysdatabases表则只要有权限访问该表就能查出所有数据库信息. 默认情况下,sqlserver提供的6个数据库中,除了model数据库外,其他若干个数据库还可能拥有guest用

C#访问SQL Server数据库的实现方法

本文实例讲述了C#访问SQL Server数据库的实现方法.分享给大家供大家参考.具体如下: 这里介绍一下用C#访问SQL Server数据库.这里要涉及到几个类:SqlConnection类.SqlCommand类.SqlDataReader类. SqlConnection类用于建立与数据库的连接,SqlCommand类用于创建SQL命令,SqlDataReader对象是执行SqlCommand对象返回结果集的查询语句后的结果. 下面是部分代码: /*其中server表示服务器,"."

C#实现Excel表数据导入Sql Server数据库中的方法

本文实例讲述了C#实现Excel表数据导入Sql Server数据库中的方法.分享给大家供大家参考,具体如下: Excel表数据导入Sql Server数据库的方法很多,这里只是介绍了其中一种: 1.首先,我们要先在test数据库中新建一个my_test表,该表具有三个字段tid int类型, tname nvarchar类型, tt nvarchar类型 (注意:my_test表中的数据类型必须与Excel中相应字段的类型一致) 2. 我们用SELECT * FROM  OPENROWSET(

C#将图片存放到SQL SERVER数据库中的方法

本文实例讲述了C#将图片存放到SQL SERVER数据库中的方法.分享给大家供大家参考.具体如下: 第一步: //获取当前选择的图片 this.pictureBox1.Image = Image.FromStream(this.openFileDialog1.OpenFile()); //获取当前图片的路径 string path = openFileDialog1.FileName.ToString(); //将制定路径的图片添加到FileStream类中 FileStream fs = ne

sql server实现分页的方法实例分析

本文实例讲述了sql server实现分页的方法.分享给大家供大家参考,具体如下: declare @index int,@num int set @index = 1--当前页 set @num = 2--单页包含的行数 --分页1 select top (@num) * from ppohd where doccode not in ( select top (@num * (@index -1)) doccode from ppohd order by doccode ) order by

五种SQL Server分页存储过程的方法及性能比较

在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览.本文我们总结了五种SQL Server分页存储过程的方法,并对其性能进行了比较,接下来就让我们来一起了解一下这一过程. 创建数据库data_Test : create database data_Test GO use data_Test GO create table tb_TestTable --创建表 ( id int identity(1,1) primary key, userN