主页 / 问题 / 989218
Accepted
Иван К
Asked:2020-06-04 22:07:52 +0000 UTC2020-06-04 22:07:52 +0000 UTC

如何在 T-SQL 中遍历树？

  • 772

我有一张表，它是文件和文件夹的结构。有字段 id、id_parent、file_name_cl。一些记录通过 id_parent 引用同一表中的其他记录（如文件夹中的文件）。我也有这样的一行：“\Nam​​e\Name2\Name3”，这是表中最终文件的路径，可以这么说。告诉我如何编写请求或函数，以便通过将这样的字符串传递给它，我可以获得我正在寻找的文件？我可以将字符串拆分为 T-SQL 之外的名称数组，并且我认为也可以将其作为表传输，但我不知道如何遍历树...

更新：原来是这样完成的：

CREATE TYPE [dbo].[FullPathType] AS TABLE(idx int, Name nvarchar(1000) not null)
GO

CREATE function [dbo].[GetFileByFullPath] (@full_path dbo.FullPathType readonly)
returns int
begin
  DECLARE @i int
  DECLARE @count_rows int
  DECLARE @current_parent_id int
  DECLARE @current_name nvarchar(1000)

  SET @i = 1
  SET @current_parent_id = null
  SET @count_rows = (SELECT COUNT(*) FROM @full_path)
  IF @count_rows > 0
    while (@i<=(select max(idx) from @full_path))
      begin
        SET @current_name = (SELECT Name FROM @full_path WHERE idx = @i)
        SET @current_parent_id = (select id from Files where file_name_cl=@current_name and id_parent=@current_parent_id)
        SET @i=@i+1
      end

RETURN @current_parent_id;
end
go
sql

1 个回答

  1. Best Answer

    在一些帮助下解决了：

    CREATE TYPE [dbo].[FullPathType] AS TABLE(idx int, Name nvarchar(1000) not null)
GO

CREATE function [dbo].[GetFileByFullPath] (@full_path dbo.FullPathType readonly)
returns int
begin
  DECLARE @i int
  DECLARE @count_rows int
  DECLARE @current_parent_id int
  DECLARE @current_name nvarchar(1000)

  SET @i = 1
  SET @current_parent_id = null
  SET @count_rows = (SELECT COUNT(*) FROM @full_path)
  IF @count_rows > 0
    while (@i<=(select max(idx) from @full_path))
      begin
        SET @current_name = (SELECT Name FROM @full_path WHERE idx = @i)
        SET @current_parent_id = (select id from Files where file_name_cl=@current_name and id_parent=@current_parent_id)
        SET @i=@i+1
      end

RETURN @current_parent_id;
end
go
    • 0

相关问题