我有一个表具有自引用外键,表示其父行.为了以最简单的形式说明问题,我们将使用此表：
CREATE TABLE Folder(
id int IDENTITY(1,1) NOT NULL,--PK
parent_id int NULL,--FK
folder_name varchar(255) NOT NULL)
我想创建一个标量值函数,它将文件夹名称的连接字符串及其所有父文件夹名称一直返回到根文件夹,该文件夹将由null parent_id值指定.
我目前的解决方案是一种程序方法,我认为这种方法并不理想.这是我正在做的事情：
CREATE FUNCTION dbo.GetEntireLineage
(@folderId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @lineage VARCHAR(MAX)
DECLARE @parentFolderId INT
SELECT @lineage = folder_name,@parentFolderId = parent_id FROM Folder WHERE id = @folderId
WHILE NOT @parentFolderId IS NULL
BEGIN
SET @parentFolderId = (SELECT parent_id FROM Folder WHERE parent_id = @parentFolderId)
SET @lineage = (SELECT @lineage + '-' + (SELECT folder_name FROM Folder WHERE parent_id = @parentFolderId))
END
RETURN @lineage
END
有更理想的方法吗？我是一名经验丰富的程序员,但T-sql对我来说不是一个熟悉的世界,我知道由于基于集合的数据的性质,这些问题通常需要不同的方法.任何帮助找到解决方案或任何其他提示和技巧来处理T-sql将非常感激.
解决方法
要确定您需要测试的性能.我已经使用您的版本(略微修改)和其他人建议的递归CTE版本进行了一些测试.
我在一个文件夹层次结构中使用了包含2048行的示例表,因此当将2048作为参数传递给函数时,完成了2048个连接.
循环版本：
create function GetEntireLineage1 (@id int)
returns varchar(max)
as
begin
declare @ret varchar(max)
select @ret = folder_name,@id = parent_id
from Folder
where id = @id
while @@rowcount > 0
begin
select @ret = @ret + '-' + folder_name,@id = parent_id
from Folder
where id = @id
end
return @ret
end
统计：
sql Server Execution Times: cpu time = 125 ms,elapsed time = 122 ms.
递归CTE版本：
create function GetEntireLineage2(@id int)
returns varchar(max)
begin
declare @ret varchar(max);
with cte(id,name) as
(
select f.parent_id,cast(f.folder_name as varchar(max))
from Folder as f
where f.id = @id
union all
select f.parent_id,c.name + '-' + f.folder_name
from Folder as f
inner join cte as c
on f.id = c.id
)
select @ret = name
from cte
where id is null
option (maxrecursion 0)
return @ret
end
统计：
sql Server Execution Times: cpu time = 187 ms,elapsed time = 183 ms.
因此,在这两者之间,循环版本更有效,至少在我的测试数据上.您需要测试您的实际数据以确定.
编辑
用于xml路径(”)技巧的递归CTE.
create function [dbo].[GetEntireLineage4](@id int)
returns varchar(max)
begin
declare @ret varchar(max) = '';
with cte(id,lvl,1,f.folder_name
from Folder as f
where f.id = @id
union all
select f.parent_id,lvl + 1,f.folder_name
from Folder as f
inner join cte as c
on f.id = c.id
)
select @ret = (select '-'+name
from cte
order by lvl
for xml path(''),type).value('.','varchar(max)')
option (maxrecursion 0)
return stuff(@ret,'')
end
统计：
sql Server Execution Times: cpu time = 31 ms,elapsed time = 37 ms.
