SQL get Child Parent

電腦小知識

–http://forums.asp.net/t/1849469.aspx

–get parent Clild

;WITH CTE AS

(

SELECT ID,Name,0 AS Level,CAST(Name AS VARCHAR(MAX)) AS ParentChild

FROM CellGroup

WHERE parentID IS NULL

UNION ALL

SELECT T1.ID,T1.Name,CTE.Level+1 AS Level,

CAST(CTE.Name+’->’+T1.Name AS VARCHAR(MAX)) AS ParentChild

FROM CellGroup AS T1

INNER JOIN CTE ON T1.parentID=CTE.ID

)

SELECT *

FROM CTE

–get top level

WITH CTE AS

(

SELECT CellGroup.ID, CellGroup.name, CellGroup.parentID, 0 [Level]

FROM CellGroup

union all

select CTE.ID, CTE.name, CellGroup.parentID, Level + 1

from CTE

INNER JOIN CellGroup

on CTE.parentID = CellGroup.ID

where CellGroup.parentID is not null

)

SELECT c.*

FROM ( SELECT *, max([Level]) OVER (PARTITION BY name) [MaxLevel]

FROM CTE

) c

WHERE MaxLevel = level

, , ,