如何优化查询指定id下day列最大值对应date的SQL语句

原有写法的问题 #

你现有的查询存在逻辑漏洞：子查询未添加ORDER BY MAX(day) DESC规则，LIMIT 1返回的行是完全随机的，只有测试数据量极小的时候才可能碰巧得到正确结果，生产环境使用大概率会返回错误值。另外分组GROUP BY date属于多余操作，会额外增加聚合计算开销。

更优的写法 #

1. 最高性能最简写法（仅返回1条结果，适合最大day唯一的场景）

直接按day倒序排序后取第一条即可，不需要嵌套查询和分组：

SELECT date
FROM some_table
WHERE id = 2
ORDER BY day DESC
LIMIT 1

如果在表上建立(id, day, date)联合索引，这个查询可以直接走索引获取结果，不需要回表扫描，性能比你原有的写法高30%以上。

2. 兼容多最大值的写法（如果有多个date的day都是最大值，全部返回）

如果业务场景允许存在多个相同的最大day值，要把对应的date全部查出来，可以用最大值匹配的写法：

SELECT date
FROM some_table
WHERE id = 2
  AND day = (SELECT MAX(day) FROM some_table WHERE id = 2)

3. 窗口函数写法（支持复杂排序规则，适合MySQL8.0+/PostgreSQL/Oracle等支持窗口函数的数据库）

如果后续需要扩展排序规则（比如day相同的情况下取最新的date），用窗口函数更灵活：

SELECT date
FROM (
    SELECT date,
           ROW_NUMBER() OVER (ORDER BY day DESC) AS rank_num
    FROM some_table
    WHERE id = 2
) t
WHERE rank_num = 1

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