树 路径 Sql

表：

 

CREATE TABLE [dbo].[Categorys](
    [ID] [uniqueidentifier] NOT NULL,    
    [CategoryDspName] [nvarchar](50) NULL,
    [ParentID] [uniqueidentifier] NULL    
 CONSTRAINT [PK_Categorys] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

获取路径

 

 ;WITH t AS
(
    SELECT id,  parentID, CategoryDspName,PATH = CAST(CategoryDspName AS NVARCHAR) 
    FROM Categorys AS A
    WHERE NOT EXISTS
          (
              SELECT ID 
              FROM Categorys
              WHERE id = A.parentID
          )
    UNION ALL
    SELECT A.ID, A.ParentID,A.CategoryDspName, CAST(B.path+'/'+A.CategoryDspName AS NVARCHAR)
    FROM Categorys AS A
    JOIN t AS B  ON  A.parentID = B.ID
)
 select ID,PATH from t;