优雅获取SQL表中实体最高层级的实现方法

没问题，我来帮你搞定这个需求～首先咱们明确核心目标：从现有的Roles表中，为每个Name找出对应的最高层级Role（也就是最顶层的角色，比如GrandParent比Parent层级更高）。因为咱们没法修改表结构，所以关键是先给自定义的层级定义一个明确的优先级排序，再基于这个排序筛选结果。

思路说明 #

首先需要给每个Role设定层级权重——权重值越大，代表层级越高。根据你给出的示例数据，层级优先级应该是：GrandParent > Parent > Child > Grandchild，咱们可以给它们分别对应4、3、2、1的权重值（如果有其他未知Role，可以设为0兜底）。

接下来就可以用SQL的窗口函数或者分组查询，基于这个权重值为每个Name筛选出权重最高的Role。

方案1：使用CTE+窗口函数（推荐，可读性高） #

这个方案适用于支持CTE和窗口函数的数据库（比如PostgreSQL、SQL Server、MySQL 8.0+等）：

WITH RoleHierarchy AS (
    SELECT 
        Name,
        Role,
        -- 给每个Role分配层级权重，数值越大层级越高
        CASE Role
            WHEN 'GrandParent' THEN 4
            WHEN 'Parent' THEN 3
            WHEN 'Child' THEN 2
            WHEN 'Grandchild' THEN 1
            ELSE 0 -- 处理未定义的Role
        END AS HierarchyLevel
    FROM Roles
),
RankedRoles AS (
    SELECT 
        Name,
        Role,
        -- 按Name分组，层级权重降序排名
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY HierarchyLevel DESC) AS RankNum
    FROM RoleHierarchy
)
SELECT Name, Role AS HighestLevelRole
FROM RankedRoles
WHERE RankNum = 1;

方案解释 #

1. RoleHierarchy CTE：给每条记录的Role赋予对应的层级权重，把字符串形式的层级转化为可排序的数值。
2. RankedRoles CTE：通过ROW_NUMBER()窗口函数，为每个Name下的Role按层级权重从高到低排名。
3. 最后筛选出排名为1的记录，就是每个Name的最高层级Role。

如果你的场景中，某个Name可能存在多个相同最高层级的Role（比如同时有两个GrandParent），可以把ROW_NUMBER()换成RANK()，这样会返回所有最高层级的记录。

方案2：使用子查询（兼容老版本数据库） #

如果你的数据库不支持CTE或者窗口函数（比如MySQL 5.x），可以用子查询的方式实现：

SELECT r.Name, r.Role AS HighestLevelRole
FROM Roles r
INNER JOIN (
    SELECT 
        Name,
        MAX(
            CASE Role
                WHEN 'GrandParent' THEN 4
                WHEN 'Parent' THEN 3
                WHEN 'Child' THEN 2
                WHEN 'Grandchild' THEN 1
                ELSE 0
            END
        ) AS MaxHierarchy
    FROM Roles
    GROUP BY Name
) mh ON r.Name = mh.Name
WHERE 
    CASE r.Role
        WHEN 'GrandParent' THEN 4
        WHEN 'Parent' THEN 3
        WHEN 'Child' THEN 2
        WHEN 'Grandchild' THEN 1
        ELSE 0
    END = mh.MaxHierarchy;

方案解释 #

1. 子查询mh：按Name分组，计算每个Name对应的最高层级权重。
2. 关联原表Roles，找到每个Name中Role权重等于最高权重的记录，也就是最高层级的Role。

内容的提问来源于stack exchange，提问作者SimaPro