用sql遍历树的问题

tanshanghua

Dept               
ID        Parent        Name
1               0               A
2               1               B
3               1               C
4               2               D
5               2               E
6               2               F
7              3               G

数据结构图:
B和C是A的子结点,
D.E和F是B的子结点,
G是C的子结点

要求输出結果：               
ID        Name        FullPath
1               A                   A
2               B                   A\\B
3               C                   A\\C
4               D                   A\\B\\D
5               E                   A\\B\\E
6               F                   A\\B\\F
7               G                   A\\C\\G

cosio

网上有现成的,你去找找!~

wchal

寫個函數返回fullpath部份

lynx286

8.1.2 树形数据分级汇总示例.sql
--测试数据
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT \'101\'  ,\'现金\'
UNION ALL SELECT \'102\'  ,\'银行存款\'
UNION ALL SELECT \'10201\',\'工行\'
UNION ALL SELECT \'10202\',\'建行\'
UNION ALL SELECT \'10203\',\'农行\'

DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT \'101\'  ,100
UNION ALL SELECT \'10201\',20
UNION ALL SELECT \'10202\',120

--逐级汇总查询
SELECT a.No,a.Name,
        [Money]=ISNULL(SUM([Money]),0)
FROM @a a
        LEFT JOIN @b b ON b.No LIKE a.No+\'%\'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No         Name        Money
---------------- ----------------- ---------------------
101        现金         100.0000
102        银行存款     140.0000
10201      工行         20.0000
10202      建行         120.0000
10203      农行         .0000
--*/

lynx286

8.1.3 树形数据编号重排的通用存储过程.sql

--重排编码的存储过程
CREATE PROC p_RTaxisCode
@TableName sysname,    --重排编码的表名
@FieldName sysname,    --编码字段名
@CodeRule varchar(100)  --以逗号分隔的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
AS
--参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N\'IsUserTable\'),0)=0
BEGIN
        RAISERROR(N\'\"%s\"不存在,或者不是用户表\',1,16,@TableName)
        RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
BEGIN
        RAISERROR(N\'列名\"%s\"在用户表\"%s\"中无效\',1,16,@FieldName,@TableName)
        RETURN       
END
IF ISNULL(@CodeRule,\'\')=\'\'
BEGIN
        RAISERROR(N\'必须编码规则字符串\',1,16)
        RETURN       
END
IF PATINDEX(N\'%[^0-9^,]%\',@CodeRule)>0
BEGIN
        RAISERROR(N\'编码规则字符串\"%s\"中只能包含数字和逗号(,)\',1,16,@CodeRule)
        RETURN       
END

--生成编码重排处理语句
DECLARE @s nvarchar(4000),@len int,@lens int
SELECT
        @TableName=QUOTENAME(@TableName),
        @FieldName=QUOTENAME(@FieldName),
        @len=LEFT(@CodeRule,CHARINDEX(N\',\',@CodeRule+N\',\')-1),
        @lens=@len,
        @CodeRule=STUFF(@CodeRule,1,CHARINDEX(N\',\',@CodeRule+N\',\'),N\'\')+N\',\',
        @s=N\'RIGHT(\'+CAST(POWER(10,@len) as varchar)
                +N\'+(SELECT COUNT(DISTINCT \'+@FieldName
                +N\') FROM \'+@TableName
                +N\' WHERE \'+@FieldName+N\'<=a.\'+@FieldName
                +N\' AND LEN(\'+@FieldName+N\')=\'+CAST(@len as varchar)
                +N\'),\'+CAST(@len as varchar)+N\')\'

WHILE LEN(@CodeRule)>1
BEGIN
        SELECT
                @len=LEFT(@CodeRule,CHARINDEX(N\',\',@CodeRule)-1),
                @s=@s
                        +N\'+CASE WHEN LEN(\'+@FieldName
                        +N\')>\'+CAST(@lens as varchar)
                        +N\' THEN RIGHT(\'+CAST(POWER(10,@len) as varchar)
                        +N\'+(SELECT COUNT(DISTINCT \'+@FieldName
                        +N\') FROM \'+@TableName
                        +N\' WHERE \'+@FieldName+N\'<=a.\'+@FieldName
                        +N\' AND \'+@FieldName+N\' LIKE LEFT(a.\'+@FieldName
                        +N\',\'+CAST(@lens as varchar)
                        +N\')+\'\'\'+REPLICATE(N\'_\',@len)
                        +N\'\'\'),\'+CAST(@len as varchar)
                        +N\') ELSE \'\'\'\' END\',
                @lens=@lens+@len,
                @CodeRule=STUFF(@CodeRule,1,CHARINDEX(N\',\',@CodeRule),N\'\')
END
EXEC(N\'UPDATE a SET \'+@FieldName+N\'=\'+@s+N\' FROM \'+@TableName+N\' a\')

lynx286

8.1.3 树形数据编号重排示例.sql

--测试数据
DECLARE @t TABLE(No varchar(10))
INSERT @t SELECT \'1\'
UNION ALL SELECT \'3\'
UNION ALL SELECT \'302\'
UNION ALL SELECT \'305\'
UNION ALL SELECT \'305001\'
UNION ALL SELECT \'305005\'
UNION ALL SELECT \'6\'
UNION ALL SELECT \'601\'

--重排编码
UPDATE a SET
        No=RIGHT(--重排第一层编码
                        (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1)
                +CASE --重排第二层编码
                        WHEN LEN(No)>1
                        THEN RIGHT(100+
                                (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+\'__\'),2)
                        ELSE \'\' END
                +CASE --重排第三层编码
                        WHEN LEN(No)>3
                        THEN RIGHT(1000+
                                (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+\'___\'),3)
                        ELSE \'\' END
FROM @t a

--显示处理结果
SELECT * FROM @t
/*--结果
No         
----------
1
2
201
202
202001
202002
3
301
--*/

lynx286

8.2.2 树形数据层次显示处理示例.sql
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT \'001\',NULL ,\'山东省\'
UNION ALL SELECT \'002\',\'001\',\'烟台市\'
UNION ALL SELECT \'004\',\'002\',\'招远市\'
UNION ALL SELECT \'003\',\'001\',\'青岛市\'
UNION ALL SELECT \'005\',NULL ,\'四会市\'
UNION ALL SELECT \'006\',\'005\',\'清远市\'
UNION ALL SELECT \'007\',\'006\',\'小分市\'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
        FROM @t a,@t_Level b
        WHERE a.PID=b.ID
                AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+\'|--\'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/


8.2.2 树形数据广度排序处理示例.sql
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT \'001\',NULL ,\'山东省\'
UNION ALL SELECT \'002\',\'001\',\'烟台市\'
UNION ALL SELECT \'004\',\'002\',\'招远市\'
UNION ALL SELECT \'003\',\'001\',\'青岛市\'
UNION ALL SELECT \'005\',NULL ,\'四会市\'
UNION ALL SELECT \'006\',\'005\',\'清远市\'
UNION ALL SELECT \'007\',\'006\',\'小分市\'

--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level
        FROM @t a,@t_Level b
        WHERE a.PID=b.ID
                AND b.Level=@Level-1
END

--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID   PID  Name      
------- --------- ----------
001  NULL 山东省
005  NULL 四会市
002  001   烟台市
003  001   青岛市
006  005   清远市
004  002   招远市
007  006   小分市
--*/


8.2.2 树形数据深度排序处理示例(递归法).sql
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT \'001\',NULL ,\'山东省\'
UNION ALL SELECT \'002\',\'001\',\'烟台市\'
UNION ALL SELECT \'004\',\'002\',\'招远市\'
UNION ALL SELECT \'003\',\'001\',\'青岛市\'
UNION ALL SELECT \'005\',NULL ,\'四会市\'
UNION ALL SELECT \'006\',\'005\',\'清远市\'
UNION ALL SELECT \'007\',\'006\',\'小分市\'
GO

--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
        DECLARE tb CURSOR LOCAL
        FOR
        SELECT ID FROM tb
        WHERE PID=@ID
                OR(@ID IS NULL AND PID IS NULL)
        OPEN TB
        FETCH tb INTO @ID
        WHILE @@FETCH_STATUS=0
        BEGIN
                INSERT @t_Level VALUES(@ID,@sort)
                SET @sort=@sort+1
                IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
                BEGIN
                        --递归查找当前节点的子节点
                        INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
                        SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
                END
                FETCH tb INTO @ID
        END
        RETURN
END
GO

--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID   PID   Name      
------ --------- ----------
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/


8.2.2 树形数据深度排序处理示例(模拟单编号法).sql

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT \'001\',NULL ,\'山东省\'
UNION ALL SELECT \'002\',\'001\',\'烟台市\'
UNION ALL SELECT \'004\',\'002\',\'招远市\'
UNION ALL SELECT \'003\',\'001\',\'青岛市\'
UNION ALL SELECT \'005\',NULL ,\'四会市\'
UNION ALL SELECT \'006\',\'005\',\'清远市\'
UNION ALL SELECT \'007\',\'006\',\'小分市\'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
        FROM @t a,@t_Level b
        WHERE a.PID=b.ID
                AND b.Level=@Level-1
END

--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID   PID   Name      
------ --------- ----------
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/