找回密码
当前位置：Mikel > 开发笔记 > 正文

[SQL]SqlServer:CTE函数处理递归(WITH语法)

2009-04-30 分类：开发笔记 / 数据库 阅读(1231) 评论(0)

     我们在做分类处理的时候，总会遇到递归的处理，比如说地区就是一个例子，中国–北京–西城区，我们可以把这样的信息存储在一个数据表中，用 ParentID区分根节点和叶子节点。假如我们要做导航，得到了”西城区”，但是还要得到他的父级，或夫父级，一种方式是用程序来处理，也是很简单，另 一种方式就是用数据库的功能。既然数据库能完成这件事，何必在用程序呢？

     在SQLServer2005以前的版本中，也能处理这种情况，不过当时用的是存储过程，代码也比较多，表设计的时候，还的加一个表示“深度”的字段，当时我也写过相关的文章，参见：

存储过程实现无限级分类（1）

存储过程实现无限级分类（2）

存储过程实现无限级分类（3）

    但是，在学习SQLServer2005新加的特性的时候，注意到WITH语句能实现这样的功能，并且代码简单，简练，最重要的是好理解。

下面是一张递归的表结构图：

Recursive_1

里边的parentID记录的就是他们之间的关系；

比如我们要查询id = 10008,并且parentID 为10008的数据怎么办呢？如：

Recursive_3

呵呵，不留悬念了，直接上代码吧！其实挺容易理解的：

WITH CategoryInfo AS(
Select id,text,parentid FROM Recursive Where id = 10008
UNION ALL
Select a.id,a.text,a.parentid FROM Recursive AS a,CategoryInfo AS b Where a.parentid = b.id
)
Select * FROM CategoryInfo

 

看看是不是简单，简练，好理解！

赶快试试吧！

这是从上向下查，如果从下向上查，怎么查呢，自己举一反三吧！

存储过程实现无限级分类（1）

    在做考题的的时候，涉及到分类，虽然不是无限级的，但是，为了以后扩展用，想做成无限级，在网上找找了，一个用存储过程作的，虽然添加，编辑，移动，用的 是存储过程，可是在读出来的时候只用了一条Select 语句，感觉挺爽的，下面我把存储过程列出来，我只用到了添加，编辑，没有用到移动。

1，表结构

===================================================
表结构：
表名：Tb_Column
表结构（所有字段非空）：
Column_ID     int 主键（注：非标识）
Column_Name   nvarchar(50)分类名称
Parent_ID     int 父分类ID（默认值0）
Column_Path   nvarchar(1000) 分类路径
Column_Depth  int分类深度(默认值0）
Column_Order  int排序(默认值0)
Column_Intro  nvarchar(1000)分类说明
================================================

2.添加的存储过程

Create PROCEDURE sp_Column_Insert
(
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
—通过现有记录获取栏目ID
Declare @Column_ID As int
Declare @Column_Depth As int
Select @Column_ID = Max(Column_ID) From Tb_Column
IF @Column_ID Is Not Null
Set @Column_ID = @Column_ID+1
Else
Set @Column_ID = 1
—判断是否是顶级栏目，设置其Column_Path和Column_Order
Declare @Column_Path As nvarchar(1000)
Declare @Column_Order As int
IF @Parent_ID = 0
Begin
Set @Column_Path =Ltrim(Str(@Column_ID))
Select @Column_Order = Max(Column_Order) From Tb_Column
IF @Column_Order Is Not Null
Set @Column_Order = @Column_Order + 1
Else —如果没有查询到记录，说明这是第一条记录
Set @Column_Order = 1
—深度
Set @Column_Depth = 1
End
Else
Begin
—获取父节点的路径和深度
Select @Column_Path = Column_Path ,@Column_Depth = Column_Depth From Tb_Column Where 
Column_ID=@Parent_ID
IF @Column_Path Is Null
Begin
Set @Err = 1
Goto theEnd
End
—获取同父节点下的最大序号
Select @Column_Order = Max(Column_Order) From Tb_PicColumn Where Column_Path like 
''+@Column_Path+'|%'  Or Column_ID = @Parent_ID
IF @Column_Order Is Not Null —如果序号存在，那么将该序号后的所有序号都加1
Begin 
—更新当前要插入节点后所有节点的序号
Update Tb_Column Set Column_Order = Column_Order +1 Where Column_Order 
>@Column_Order
—同父节点下的最大序号加上1，构成自己的序号
Set @Column_Order = @Column_Order + 1
End 
Else
Begin
Set @Err=1
Goto theEnd
End
—父节点的路径加上自己的ID号，构成自己的路径
Set @Column_Path = @Column_Path + '|' + Ltrim(Str(@Column_ID))
—深度
Set @Column_Depth = @Column_Depth+1
End
Insert Into Tb_Column(Column_Name,Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Intro) 
Values(@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column_Order,@Column_Intro)
IF @@Error<>0 
Begin
Set @Err=1
Goto theEnd
End
—更新当前记录之后的记录的ORDER
—Update Tb_Column Set Column_Order = Column_Order+1 Where Column_Order  > @Column_Order 
theEnd:
IF @Err=0
Begin
Commit Tran
Return @Column_ID
End
Else
Begin
    Rollback Tran
Return 0
End
GO

存储过程实现无限级分类（2）
di。删除的存储过程

Create PROCEDURE sp_Column_Delete
(
@Column_ID int
)
AS
Declare @Err As int
Set @Err = 0
Begin Tran
—首先查询该节点下是否有子节点
Select Column_ID From Tb_Column Where Parent_ID = @Column_ID
IF @@RowCount<>0
    Begin
    Set @Err = 1
    Goto theEnd
    End
—获取该节点的Column_Order,为了删除后整理其他记录的顺序
Declare @Column_Order As int
Select @Column_Order = Column_Order From Tb_Column Where Column_ID = @Column_ID
IF @Column_Order Is NUll
    Begin
      Set @Err =2
      Goto theEnd
    End 
—更新其他记录的Column_Order
Update Tb_Column Set Column_Order = Column_Order –1 Where Column_Order >@Column_Order 
IF @@Error<>0
    Begin
      Set @Err =3
      Goto theEnd
    End 
—删除操作
Delete From Tb_Column Where Column_ID=@Column_ID
IF @@Error<>0
    Begin
      Set @Err =4
      Goto theEnd
  End 
—更新其他记录的Column_ID
—Update Tb_Column Set Column_ID= Column_ID – 1 Where Column_ID >@Column_ID 
—IF @@Error<>0
—    Begin
—      Set @Err =5
—      Goto theEnd
—    End 
theEnd:
IF @Err = 0 
    Begin
      Commit Tran
      Return 0 —删除成功
    End
Else
    Begin
      IF @Err=1
  Begin
      Rollback Tran
      Return 1 —有子节点
End
      Else
Begin
      Rollback Tran
      Return 2—未知错误
End
    End
GO

4.编辑的存储过程（没有用到，我自己写了一个简单的只是编辑名称，没有涉及到移动）

Create PROCEDURE sp_Column_Update
(
@Column_ID int,
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
—获取修改前的：Parent_ID,Column_Depth,Column_Order
Declare @oParent_ID As int
Declare @oColumn_Depth As int
Declare @oColumn_Order As int
Declare @oColumn_Path As nvarchar(1000)
Select @oParent_ID = Parent_ID, @oColumn_Depth = Column_Depth,@oColumn_Order = Column_Order, @oColumn_Path = Column_Path  From Tb_Column Where Column_ID = @Column_ID
IF @oParent_ID Is Null
    Begin
    Set @Err = 1
    Goto theEnd
    End
—如果父ID没有改变，则直接修改栏目名和栏目简介
IF @oParent_ID = @Parent_ID
    Begin
    Update Tb_Column Set Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
    IF @@Error <> 0
    Set @Err = 2
    Goto theEnd
    End
Declare @nColumn_Path As nvarchar(1000)
Declare @nColumn_Depth As int
Declare @nColumn_Order As int
—获取当前节点作为父节点所包含的节点数[包括自身] 注：如果返回 “1” 说明是单节点
Declare @theCount As int
Select @theCount = Count(Column_ID) From Tb_Column Where Column_ID=@Column_ID Or Column_Path like ''+@oColumn_Path+'|%'
IF @theCount Is Null
Begin
    Set @Err = 3
    Goto theEnd
End 
IF @Parent_ID=0 —如果是设置为顶级节点，将节点设置为最后一个顶级节点
Begin
—Print '设置为顶级栏目'
Set @nColumn_Path = Ltrim(Str(@Column_ID))
Set @nColumn_Depth =1
Select @nColumn_Order = Max(Column_Order) From Tb_Column
IF @nColumn_Order Is NULL
                  Begin
     Set @Err = 4
     Goto theEnd
     End 
Set @nColumn_Order = @nColumn_Order – @theCount + 1
—更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
—Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：Column_Order'
Update Tb_Column Set Column_Order = Column_Order–@theCount Where (Column_Order >@oColumn_Order) And (Column_Path Not like ''+@oColumn_Path+'|%')
IF @@Error <> 0
    Begin
    Set @Err = 7
    Goto theEnd
    End
—Print '更新本栏目的：Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro'
Print 'Order : '+Ltrim(Str(@nColumn_Order))
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
    Begin
    Set @Err = 5
    Goto theEnd
    End
—Print '更新本栏目下的所有子栏目的：Column_Path,Column_Depth,Column_Order'
             Update Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth–@oColumn_Depth),Column_Order = Column_Order+( @nColumn_Order–@oColumn_Order) Where Column_Path like ''+@oColumn_Path+'|%'
IF @@Error <> 0
    Begin
    Set @Err = 6
    Goto theEnd
    End
End 
Else
Begin
—获取未来父节点的相关信息,并设置本节点的相关值
Select @nColumn_Depth = Column_Depth,@nColumn_Path = Column_Path From Tb_Column Where Column_ID = @Parent_ID
IF @nColumn_Depth Is  NULL Or @nColumn_Path Is Null
      Begin
      Set @Err = 8
      Goto theEnd
      End 
Set @nColumn_Depth = @nColumn_Depth +1
Select @nColumn_Order =Max(Column_Order) From Tb_Column Where Column_ID = @Paren
 
t_ID Or  Column_Path like ''+@nColumn_Path+'|%'
IF @nColumn_Order Is  NULL
      Begin
      Set @Err = 9
      Goto theEnd
      End 
Set @nColumn_Path = @nColumn_Path +'|'+ Ltrim(Str(@Column_ID))
IF @nColumn_Order = @oColumn_Order+1 —如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
                    Begin
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
    Begin
    Set @Err = 10
    Goto theEnd
    End
       End
Set  @nColumn_Order = @nColumn_Order + 1 
—更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身  3 所有子节点
—分为向上移或象下移
—Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置]  [不包括本栏目下的子栏目]的：Column_Order'
IF @nColumn_Order < @oColumn_Order
Begin
Update Tb_Column Set Column_Order = Column_Order+@theCount Where Column_Order<@oColumn_Order  And Column_Order >=@nColumn_Order And (Column_Path Not like ''+@oColumn_Path+'|%') And Column_ID<>@Column_ID
IF @@Error <> 0
        Begin
        Set @Err = 12
        Goto theEnd
        End
End
Else
Begin
Update Tb_Column Set Column_Order = Column_Order–@theCount Where Column_Order >@oColumn_Order And Column_Order<@nColumn_Order  And (Column_Path Not like ''+@oColumn_Path+'|%') And Column_ID<>@Column_ID
IF @@Error <> 0
        Begin
        Set @Err = 13
        Goto theEnd
        End
End
—Print '更新本栏目的：Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro'
Print 'Order : '+Ltrim(Str(@nColumn_Order))
IF @nColumn_Order > @oColumn_Order
Set @nColumn_Order = @nColumn_Order – @theCount
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
    Begin
    Set @Err = 10
    Goto theEnd
    End
—Print '更新本栏目下的所有子栏目的：Column_Paht,Column_Depth,Column_Order'
             Update Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth–@oColumn_Depth),Column_Order = Column_Order+(@nColumn_Order–@oColumn_Order) Where Column_Path like ''+@oColumn_Path+'|%'
IF @@Error <> 0
    Begin
    Set @Err = 11
    Goto theEnd
    End
End
theEnd:
IF @Err<>0 —如果有错误则返回错误号
   Begin
   Rollback Tran
   Return @Err
   End
Else     —如果没有错误就返回0
   Begin
   Commit Tran
   Return 0
   End
GO

 

存储过程实现无限级分类（3）

最后一步显示分类（只是一条select语句）

Create PROCEDURE sp_Column_List 
 AS
Select Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth, 
      Column_Order, Column_Intro
FROM Tb_Column
orDER BY Column_Order
GO

接下来就是在界面呈现了 ，    显示的时候用的是DataGrid

Html代码如下：

<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" Width="520px" DataKeyField="Column_Id">
                                <Columns>
                                    <asp:TemplateColumn HeaderText="分类信息">
                                        <ItemTemplate>
                                            <asp:Label id="lbname" runat="server"></asp:Label>
                                        </ItemTemplate>
                                        <EditItemTemplate>
                                            <asp:TextBox id="tbdgname" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Column_Name")%>'>
                                            </asp:TextBox>
                                        </EditItemTemplate>
                                    </asp:TemplateColumn>
                                    <asp:TemplateColumn HeaderText="添加子节点">
                                        <ItemTemplate>
                                            <a href="#" onclick="Open('AddSonCate.aspx?fid=<%#DataBinder.Eval(Container.DataItem,"Column_Id")%>','son',280,80)">添加子分类</a>
                                        </ItemTemplate>
                                    </asp:TemplateColumn>
                                    <asp:TemplateColumn HeaderText="编辑">
                                        <ItemTemplate>
                                            <asp:LinkButton runat="server" Text="编辑" CommandName="Edit" CausesValidation="false"></asp:LinkButton>
                                        </ItemTemplate>
                                        <EditItemTemplate>
                                            <asp:LinkButton runat="server" Text="更新" CommandName="Update" CausesValidation="False"></asp:LinkButton>&nbsp;
                                            <asp:LinkButton runat="server" Text="取消" CommandName="Cancel" CausesValidation="false"></asp:LinkButton>
                                        </EditItemTemplate>
                                    </asp:TemplateColumn>
                                    <asp:TemplateColumn HeaderText="删除">
                                        <ItemTemplate>
                                            <asp:LinkButton runat="server" ID="lbdelete" Text="删除" CommandName="Delete" CausesValidation="false" CommandArgument='<%#DataBinder.Eval(Container.DataItem,"Column_Id")%>'>
                                            </asp:LinkButton>
                                        </ItemTemplate>
                                    </asp:TemplateColumn>
                                </Columns>
                            </asp:DataGrid>

 

最关键的地方在DataGrid_ItemDataBind事件中

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
        {
            if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                //得到Column_Name字段的值
                string columnName = (string)DataBinder.Eval(e.Item.DataItem,    "Column_Name");
                string columnTemp = "";
                //得到深度值Column_Depth
                int columnDepth = Int32.Parse(DataBinder.Eval(e.Item.DataItem,"Column_Depth").ToString());
                if(columnDepth>1)
                {
                    for(int i = 1;i<columnDepth;i++)
                    {
                        columnTemp +="&nbsp;&nbsp;&nbsp;&nbsp;";
                    }
                    columnTemp+="┝";
                }
                Label lbname = (Label)e.Item.FindControl("lbname");
                lbname.Text = columnTemp+columnName ;
                LinkButton lbdelete = (LinkButton)e.Item.FindControl("lbdelete");
                lbdelete.Attributes.Add("OnClick","JavaScript:return confirm('确实要删掉此分类吗？');");
            }                
        }

赞(0) 打赏
分享到： 更多 (0)
标签：

相关推荐

热门标签

C# (813)ASP.NET MVC (619)Android (587)ASP.NET (447)数据库 (299)JavaScript (285)JQuery (221)PHP (210)SQLServer (197)程序开发 (156)架构设计 (155)源价值 (155)SQL (128)Java (111)EasyUi (85)教程 (76)网站 (67)Debug (63)互联网 (63)MySQL (60)Flash (56)搜索优化 (54)创意 (53)SEO (51)ASP.NET MVC3 (51)电子商务 (50)建站经验 (49)实例代码 (49)源码下载 (47)原创 (45)

分类

链接表

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏