查询子父级数据

WITH Pro AS( SELECT ProjectID,ProjectName,HiProjectID,Layer=1,Code=replicate('0',4-Len(ProjectID))+CONVERT(varchar,ProjectID) FROM ssProject WHERE HiProjectID=0 UNION ALL SELECT p.ProjectID,p.ProjectName,p.HiProjectID,Layer=pro.Layer+1,Code=Pro.Code+replicate('0',4-Len(p.ProjectID))+CONVERT(varchar,p.ProjectID) FROM ssProject p  JOIN Pro ON p.HiProjectID=Pro.ProjectID ) SELECT ProjectID,ProjectName=replicate('--',Layer)+ProjectName FROM pro ORDER BY Code