SQL Server 递归查询_sqlserver递归合计-CSDN博客

本文介绍了一个SQL Server 2005存储过程，用于递归地查找指定节点的所有子节点。该过程首先创建一个临时表来存储子节点ID，然后使用循环不断查找并插入更深层次的子节点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这是我在项目中用到的一个存储过程，用来通过当前节点ID递归查找其所有的子节点。需要申明的是本存储过程仅支持SQL Server 2005

--建表
GO
/****** 对象:  Table [dbo].[sys_Group]    脚本日期: 01/17/2008 16:29:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sys_Group](
    [GroupID] [int] IDENTITY(1,1) NOT NULL,
    [G_CName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [G_ParentID] [int] NOT NULL DEFAULT ((0)),
    [G_ShowOrder] [int] NOT NULL DEFAULT ((0)),
    [G_Level] [int] NULL,
    [G_ChildCount] [int] NULL,
    [G_Delete] [tinyint] NULL,
CONSTRAINT [PK_SYS_GROUP] PRIMARY KEY CLUSTERED
(
    [GroupID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类ID号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'GroupID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类中文说明' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_CName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级分类ID0:为最高级' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_ParentID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'显示顺序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_ShowOrder'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前分类所在层数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_Level'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前分类子分类数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_ChildCount'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否删除1:是0:否' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_Group', @level2type=N'COLUMN', @level2name=N'G_Delete'