132.树形数据深度排序处理-案例

水深无声 2024-02-19 19:08 58阅读 0赞

### 1.树形数据深度排序处理-案例（递归法） ###

--测试数据
    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   小分市
    --*/

### 2.树形数据深度排序处理-案例（模拟单编号法） ###

--测试数据
    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   小分市
    --*/