使用MS SQL Server根据项目顺序和父类别顺序对项目进行排序

WITH CTE AS (
    SELECT ProjectID, ProjectName, ParentCategoryID, 0 AS Level
    FROM Projects
    WHERE ParentCategoryID IS NULL  -- 获取顶级项目
    UNION ALL
    SELECT p.ProjectID, p.ProjectName, p.ParentCategoryID, c.Level + 1
    FROM Projects p
    INNER JOIN CTE c ON p.ParentCategoryID = c.ProjectID
)
SELECT * FROM CTE

WITH CTE AS (
    SELECT ProjectID, ProjectName, ParentCategoryID, 0 AS Level
    FROM Projects
    WHERE ParentCategoryID IS NULL  -- 获取顶级项目
    UNION ALL
    SELECT p.ProjectID, p.ProjectName, p.ParentCategoryID, c.Level + 1
    FROM Projects p
    INNER JOIN CTE c ON p.ParentCategoryID = c.ProjectID
)
SELECT *
FROM CTE
ORDER BY Level, ParentCategoryOrder, Order