遍历+游标实现模拟oracle树结构start with 函数

背景:根据id往下寻找层级数据处理

 

在数据处理中，很多时候会遇到层级结构表，如下：

 在这种表上，要找出每一条的数据结构

实现后效果

表数据SQL

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[层级结构表]') AND type IN ('U'))
    DROP TABLE [dbo].[层级结构表]
GO

CREATE TABLE [dbo].[层级结构表] (
  [id] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [up] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [down] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [path] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[层级结构表] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of 层级结构表
-- ----------------------------
INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'5', N'4', N'', N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'4', N'3', N'5', N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'3', N'2', N'4', N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'2', N'1', N'3', N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'1', N'0', N'2', N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'8', N'7', NULL, N'')
GO

INSERT INTO [dbo].[层级结构表] ([id], [up], [down], [path]) VALUES (N'7', N'6', N'8', N'')
GO

数据处理SQL 

---创建游标
DECLARE cur_cj CURSOR LOCAL FORWARD_ONLY DYNAMIC for 
   select id  from 层级结构表;
 
 
 
 ---打开游标
 open cur_cj ;
 
 fetch  NEXT FROM cur_cj INTO @id
 
 
 WHILE @@FETCH_STATUS=0
 Begin 
                    ---操作先内部遍历一遍找出该id的相关层级,后逐行运行。
        with t as ( select id ,up  from 层级结构表  x where x.id = @id   union all  select a.id , a.up   from 层级结构表 a inner join t b on a.id = b.up  ) 

, t2 as (select * from (select distinct id from t) a )
, t3 as ( select distinct stuff((select '-'+ id  from t where 1 = 1    FOR XML path('') ),1,1,'') path from t2 b )
  UPDATE 层级结构表 SET path= (select path from t3) WHERE id=@id;

        
    -- 取下一条记录
    FETCH NEXT FROM cur_cj INTO @id
END

-- 关闭游标
CLOSE cur_cj;

-- 释放游标
DEALLOCATE cur_cj;

 

 

 

 代码参考如上。里面仅仅实现了往下寻找层级。