MySQL GROUP BY关联查询结果错误：无法获取公司最新创建时间主管

原查询的错误点 #

你的查询核心问题在于GROUP BY的使用不符合规范：

• 当你用GROUP BY Company.IdCompany分组后，SELECT语句中除了分组键（Company.IdCompany）和聚合函数（MAX(Supervisor.CreationTime)）之外，其他列（比如主管的姓名、ID，关联表的字段等）都属于非聚合、非分组的列。
• 对于这类列，数据库会随机返回分组内的任意一行数据，而非与MAX(CreationTime)对应的那条记录，这就是你拿到旧主管的原因。

正确的解决方案 #

以下提供两种通用的解决方法，适用于大多数主流数据库（如MySQL 8+、PostgreSQL、SQL Server等）：

方法1：使用窗口函数ROW_NUMBER()（推荐） #

通过窗口函数按公司分组，给每个公司的主管按创建时间倒序排名，取排名第一的就是最新主管：

WITH RankedSupervisors AS (
    SELECT 
        c.*,
        s.*,
        sc.*,
        -- 按公司分组，创建时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (
            PARTITION BY c.IdCompany 
            ORDER BY s.CreationTime DESC
        ) AS rn
    FROM Company c
    INNER JOIN SupervisorCompany sc ON c.IdCompany = sc.IdCompany
    INNER JOIN Supervisor s ON sc.IdSupervisor = s.IdSupervisor
)
-- 筛选出每个公司排名第一的主管
SELECT * 
FROM RankedSupervisors 
WHERE rn = 1;

如果同一个公司存在多个创建时间相同的最新主管，想全部返回，可以把ROW_NUMBER()换成RANK()。

方法2：子查询匹配最新创建时间 #

先通过子查询算出每个公司对应的最新主管创建时间，再关联原表找到对应记录：

SELECT c.*, s.*, sc.*
FROM Company c
INNER JOIN SupervisorCompany sc ON c.IdCompany = sc.IdCompany
INNER JOIN Supervisor s ON sc.IdSupervisor = s.IdSupervisor
-- 子查询获取每个公司的最新主管创建时间
INNER JOIN (
    SELECT 
        sc_inner.IdCompany,
        MAX(s_inner.CreationTime) AS LatestCreationTime
    FROM SupervisorCompany sc_inner
    INNER JOIN Supervisor s_inner ON sc_inner.IdSupervisor = s_inner.IdSupervisor
    GROUP BY sc_inner.IdCompany
) AS latest 
    ON c.IdCompany = latest.IdCompany 
    AND s.CreationTime = latest.LatestCreationTime;

注意：如果同一公司有多个主管创建时间相同且都是最新的，这个方法会返回所有符合条件的记录。

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