递归 往上
Create FUNCTION [dbo].[fnGetCategoryPath_Parent](@id VARCHAR(36))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @re VARCHAR(1000);
WITH categoryTemp --递归
AS (SELECT CategoryName,Id,ParentId,1 romnum FROM category
WHERE Id = @id --查询当前部门
UNION ALL
SELECT B.CategoryName,B.Id,B.ParentId,A.romnum+1 romnum
FROM categoryTemp A
INNER JOIN category B
ON B.Id = A.ParentId)
SELECT @re= (SELECT ‘\‘+ CategoryName
FROM categoryTemp ORDER BY romnum DESC FOR XML PATH(‘‘)) --获取递归后的集合
RETURN @re
END
GO
eg：
SELECT c1.CategoryId 类目ID,c1.CategoryName 类目名称, dbo.fnGetCategoryPath_Parent(c1.Id) 类目路径 FROM dbo.category c1 WHERE c1.ParentId IS NOT NULL AND c1.ParentId<>‘‘ AND NOT EXISTS(SELECT 1 FROM dbo.category temp WHERE temp.ParentId=c1.Id)
原文：https://www.cnblogs.com/kongxp/p/15093262.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群