多表查询获取每个项目对应最大idState值的SQL问题

原SQL存在的问题 #

• 过滤条件逻辑错误：将idProject字段与子查询返回的MAX(idState)值直接比对，字段语义不匹配，筛选逻辑完全不生效
• 子查询关联规则错误：子查询按idState关联，没有按idProject分组计算最大值，无法得到每个项目对应的最大状态ID
• 带ON条件的CROSS JOIN等价于INNER JOIN，语义上直接用INNER JOIN更清晰

修正方案 #

方案1：子查询先分组取最大值（兼容所有SQL数据库）

先通过分组聚合得到每个idProject对应的最大idState，再关联原表得到完整记录：

SELECT e.idState, e.delivery, ep.idProject
FROM State e
INNER JOIN StatexProject ep ON e.idState = ep.idState
INNER JOIN (
    -- 子查询计算每个项目的最大状态ID
    SELECT idProject, MAX(idState) AS max_idState
    FROM StatexProject
    GROUP BY idProject
) project_max_state 
ON ep.idProject = project_max_state.idProject 
AND ep.idState = project_max_state.max_idState

如果同一个项目存在多条相同最大idState的记录，该写法会返回所有符合条件的记录。

方案2：窗口函数实现（支持MySQL 8.0+/PostgreSQL/SQL Server等主流新版本数据库）

通过排序窗口函数直接按项目分组排序，取每组第一条即可：

SELECT idState, delivery, idProject
FROM (
    SELECT 
        e.idState, 
        e.delivery, 
        ep.idProject,
        -- 按项目分组，状态ID倒序排列，排名第一的就是最大值对应的记录
        ROW_NUMBER() OVER (PARTITION BY ep.idProject ORDER BY e.idState DESC) AS rank_num
    FROM State e
    INNER JOIN StatexProject ep ON e.idState = ep.idState
) temp
WHERE rank_num = 1

如果需要返回同一个项目下所有最大idState的记录，把ROW_NUMBER替换为RANK即可。

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