Sqlserver

递归 往上

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)