跨表分组时用Left Join技巧获取最大/最小行的实现问题

问题分析与修正方案 #

你的核心思路是对的——利用左自连接后NULL值筛选最大/最小行，但原查询的连接逻辑出错了，导致无法正确遍历同组内的所有记录来比较日期。下面是修正后的查询，完全符合你的要求（无聚合、无子查询，适配MySQL）：

SELECT p1.grp, p1.fk_mess, m1.id, m1.`when`
FROM messjoin p1
INNER JOIN messages m1 ON p1.fk_mess = m1.id
LEFT JOIN messjoin p2 ON p2.grp = p1.grp  -- 关联同组的所有messjoin记录
LEFT JOIN messages m2 ON p2.fk_mess = m2.id AND m1.`when` < m2.`when`  -- 找同组内日期更大的message
WHERE m2.id IS NULL;  -- 筛选出没有更大日期的行（即组内最大日期）

原查询的问题所在 #

你之前的查询中，inner join messjoin p2 on p2.fk_mess = m1.id这一步把p2和当前的m1绑定死了，相当于每个p1只能关联到自己对应的那条messjoin记录，根本没办法和同组内的其他记录做日期比较。而修正后的逻辑是：

1. 先通过p1关联到对应的message（m1）
2. 左连接同组的所有messjoin（p2），这样就能拿到当前组的所有message关联记录
3. 再左连接这些p2对应的message（m2），并添加条件m1.when < m2.when——只有当m1的日期不是组内最大时，才会找到对应的m2
4. 最后筛选m2.id IS NULL的行，这些就是组内没有更大日期的记录，也就是我们要的每组最大日期行

运行结果 #

执行上述查询后，会得到你期望的结果：

+------+---------+------+------------+
| grp  | fk_mess | id   | when       |
+------+---------+------+------------+
|    1 |       3 |    3 | 2000-08-16 |
|    2 |       6 |    6 | 2000-08-19 |
+------+---------+------+------------+

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