澳门金沙vip 1

【澳门金沙vip】sql 函数实现二种老爹和儿子递归

示例问题如下: 表结构:IdParentId 10 21 32 ……针对该表结构解释如下:
1的父节点为0, 2的父节点为1, 3的父节点为2 ……
以此类推,要求给定一个父节点的值,比如1,
用SQL语句查询的到该父结点下的所有子节点
下面的Sql是在SqlServer下调试通过的,如果是Oracle,则有ConnectBy可以实现.
建立测试表: DropTableDbTree CreateTableDbTree ( [Id]Int,
[Name]NVarChar(20), [ParentId]Int ) 插入测试数据:
InsertIntoDbTree([Id],[ParentId])Values(1,0)
InsertIntoDbTree([Id],[ParentId])Values(2,1)
InsertIntoDbTree([Id],[ParentId])Values(3,1)
InsertIntoDbTree([Id],[ParentId])Values(4,3)
InsertIntoDbTree([Id],[ParentId])Values(5,4)
InsertIntoDbTree([Id],[ParentId])Values(6,7)
InsertIntoDbTree([Id],[ParentId])Values(8,5) 实现方法一: 代码如下:
Declare@IdInt Set@Id=1—在次修改父节点
Select*Into#TempFromDbTreeWhereParentIdIn(@Id)
Select*Into#AllRowFromDbTreeWhereParentIdIn(@Id)–1,2
WhileExists(Select*From#Temp) Begin Select*Into#Temp2From#Temp
TruncateTable#Temp
InsertInto#TempSelect*FromDbTreeWhereParentIdIn(SelectIdFrom#Temp2)
InsertInto#AllRowSelect*From#Temp DropTable#Temp2 End
Select*From#AllRowOrderById DropTable#Temp DropTable#AllRow
实现方法二: 代码如下: CreateTable#AllRow ( IdInt, ParentIdInt )
Declare@IdInt Set@Id=1—在次修改父节点 Delete#AllRow –顶层自身
InsertInto#AllRow(Id,ParentId)Select@Id,@Id While@@RowCount0 Begin
InsertInto#AllRow(Id,ParentId) SelectB.Id,A.Id From#AllRowA,DbTreeB
WhereA.Id=B.ParentIdAnd
NotExists(SelectIdFrom#AllRowWhereId=B.IdAndParentId=A.Id) End
DeleteFrom#AllRowWhereId=@Id Select*From#AllRowOrderById
DropTable#AllRow 实现方法三: 代码如下:
在SqlServer2005中其实提供了CTE[公共表表达式]来实现递归:
关于CTE的使用请查MSDN Declare@IdInt Set@Id=3;—在次修改父节点
WithRootNodeCTE(Id,ParentId) As (
SelectId,ParentIdFromDbTreeWhereParentIdIn(@Id) UnionAll
SelectDbTree.Id,DbTree.ParentIdFromRootNodeCTE InnerJoinDbTree
OnRootNodeCTE.Id=DbTree.ParentId ) Select*FromRootNodeCTE

–在SQL Server
中其实提供了CTE[公共表表达式]来实现递归:    

在实际运用中经常会创建这样的结构表Category(Id, ParentId,
Name),特别是用于树形结构时(菜单树,权限树..),这种表设计自然而然地会用到递归,若是在程序中进行递归(虽然在程序中递归真的更方便一些),无论是通过ADO.NET简单sql查找还是ORM属性关联都会执行多次sql语句,难免会造成一些性能上的损耗,所以干脆使用sql的函数来解决这个问题,用函数返回我们最终需要的结果。

  • Declare @Id Int 
      
  • Set @Id = 24;    —在此修改父节点    
  •   
  • With RootNodeCTE(Id,ParentId) 
      
  • As    
  • (    
  • Select Id,ParentId From Dept Where ParentId In (@Id) 
      
  • Union All 
      
  • Select Dept.Id,Dept.ParentId From RootNodeCTE 
      
  • Inner Join Dept
      
  • On RootNodeCTE.Id = Dept.ParentId 
      
  • )    
  •   
  • Select * From RootNodeCTE
      

针对这类需求,这里我列出三种常用的递归:

 表结构(数据为纵向的):

  1. 以一个节点为基点,列出所有子节点直到无子 (找下级)
    。这有点儿像点兵点将,主帅只有一个,下面是左将、右将,左将下面又有千夫长、百夫长,点兵时主帅下令集合,下面的将军只管各自的队伍。
  2. 以一个节点为基点,列出所有父节点直到祖先(找上级) 。
  3. 面包屑导航数据(单条数据)

/****** Object:  Table [dbo].[Sys_Role]    Script Date:
2015/2/16 13:17:55 ******/ SET ANSI_NULLS ON GO

下面我以一幅图列出这三种形式(实线表现的是我们最终想要的数据,第三幅图中只有一条数据):

SET QUOTED_IDENTIFIER ON GO

澳门金沙vip 1

CREATE TABLE [dbo].[Sys_Role](  

OK,现在让我们来实现这几个需求,step by step。

               [ID] [nvarchar](36) [PRIMARY] NOT NULL,

  1. 数据准备

               [RoleName] [nvarchar](20) NOT NULL,  

根据上面的图中的数据创建表结构和测试数据