SQL查询取最大创建时间对应字段 无需group by聚合如何实现

原SQL报错是因为SQL的分组规则要求：SELECT后的字段要么属于GROUP BY列表，要么被聚合函数包裹。直接对color取max无法关联到最新创建时间对应的记录，三种实现方式如下：

方案1：窗口函数实现（推荐，适配MySQL 8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

通过窗口函数给同个房屋下的房间按创建时间倒序排名，取排名第一的最新房间对应的颜色即可，无需对color做聚合或分组：

SELECT h.house_id, h.street_name, r.create_date, r.color
FROM house h
INNER JOIN (
    SELECT 
        house_id,
        color,
        create_date,
        ROW_NUMBER() OVER (PARTITION BY house_id ORDER BY create_date DESC) AS rn
    FROM room
) r ON h.house_id = r.house_id
WHERE h.house_id = 5 AND r.rn = 1;

如果存在多个房间同时达到最大创建时间，只需要返回任意一条时该方案适用；如果需要返回所有最新的房间，将ROW_NUMBER()替换为RANK()即可。

方案2：子查询匹配最新时间（全数据库版本兼容，适配MySQL 5.x等旧版本） #

先查询目标房屋下房间的最大创建时间，再关联匹配对应时间的房间记录：

SELECT h.house_id, h.street_name, r.create_date, r.color
FROM house h
INNER JOIN room r ON h.house_id = r.house_id
WHERE h.house_id = 5
AND r.create_date = (SELECT MAX(create_date) FROM room WHERE house_id = 5);

如果同一时间有多个最新房间，该查询会返回所有符合条件的记录，仅需一条时可以在末尾加LIMIT 1。

方案3：单场景定向优化（仅查单个房屋时性能最优） #

因为你的需求固定查询house_id=5的房屋，可以先直接取出该房屋下最新的1条房间记录，再关联房屋表取字段：

SELECT h.house_id, h.street_name, r.create_date, r.color
FROM (
    SELECT house_id, color, create_date
    FROM room
    WHERE house_id = 5
    ORDER BY create_date DESC
    LIMIT 1
) r
INNER JOIN house h ON r.house_id = h.house_id;

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