基于一对多关联表的最大值计算列的SQL实现

我有两个通过外键关联的表，实体类定义如下：

class Account {
    [KEY]
    Guid ID { get; set; }

    // calculated from highest record in Role table with same ID
    int HighestRole { get; set; } 

    [ForeignKey("ID")]
    ICollection<R> Roles { get; set; }
}


class Role {
    [KEY]
    Guid ID { get; set; }

    int RoleID { get; set; }
}

一个账户可拥有多个角色，我希望为每个账户记录计算其关联Role表中的最高角色值。但尝试的SQL语句返回的是Roles表中所有RoleID的最大值，而非对应账户关联角色的最高RoleID（例如RoleID最大值为2，部分用户的最高RoleID应为1，但查询结果全部显示为2）。

尝试的SQL语句：

select 
    ID,
    (select MAX(RoleID) from Roles where Roles.ID = ID)
from Accounts

问题出在子查询的字段歧义上：Roles.ID = ID里的第二个ID未指定表名，数据库会将其解析为Roles.ID，导致子查询始终计算整个Roles表的最大RoleID。

以下两种方式可以解决：

方式一：明确指定字段所属表 #

给Accounts表的ID添加表前缀，清晰关联账户与角色的对应关系：

select 
    a.ID,
    (select MAX(r.RoleID) from Roles r where r.ID = a.ID) as HighestRole
from Accounts a

方式二：使用分组关联查询 #

通过LEFT JOIN结合GROUP BY实现，可读性更强，适合复杂关联场景：

select 
    a.ID,
    MAX(r.RoleID) as HighestRole
from Accounts a
left join Roles r on r.ID = a.ID
group by a.ID

• 若需要保留无角色的账户记录，上述两种方式均适用（此时无角色账户的HighestRole为NULL）；
• 若仅需查询有角色的账户，可将LEFT JOIN替换为INNER JOIN。

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