按日期关联表报错求助：GROUP BY与JOIN异常，需获取指定日期金额

错误原因 #

你的SQL报错主要有两个核心问题：

1. 聚合函数不能用在JOIN的ON条件中：数据库执行SQL的顺序是先完成JOIN关联，再执行GROUP BY分组计算聚合值。你在ON子句里写max(a.date) = b.date时，max(a.date)还未计算生成，数据库无法识别这个动态值，必然报错。
2. SELECT字段不符合分组规则：b.amount既没有用聚合函数（比如MAX、SUM）处理，也不在GROUP BY列表中，严格SQL模式下会直接报错——分组后每个id可能对应多条table2的金额记录，数据库无法确定返回哪一条。

正确写法 #

要实现获取每个id的最大日期、前一天日期，以及这两个日期对应table2金额的需求，需要先计算每个id的关键日期，再通过两次关联table2分别拉取对应金额：

方法1：使用CTE（可读性更高，推荐） #

WITH id_date_info AS (
    SELECT
        id,
        MAX(date) AS maxdate,
        MAX(date) - INTERVAL 1 DAY AS lastdate
    FROM table1
    GROUP BY id
)
SELECT
    t.id,
    t.maxdate,
    t.lastdate,
    b1.amount AS maxdate_amount,
    b2.amount AS lastdate_amount
FROM id_date_info t
LEFT JOIN table2 b1 ON t.id = b1.id AND t.maxdate = b1.date
LEFT JOIN table2 b2 ON t.id = b2.id AND t.lastdate = b2.date;

方法2：使用子查询（兼容不支持CTE的老版本数据库） #

SELECT
    t.id,
    t.maxdate,
    t.lastdate,
    b1.amount AS maxdate_amount,
    b2.amount AS lastdate_amount
FROM (
    SELECT
        id,
        MAX(date) AS maxdate,
        MAX(date) - INTERVAL 1 DAY AS lastdate
    FROM table1
    GROUP BY id
) t
LEFT JOIN table2 b1 ON t.id = b1.id AND t.maxdate = b1.date
LEFT JOIN table2 b2 ON t.id = b2.id AND t.lastdate = b2.date;

逻辑说明 #

1. 先通过子查询/CTE计算每个id的maxdate（table1中的最大日期）和lastdate（maxdate的前一天），得到每个id的核心日期集合。
2. 两次关联table2：第一次关联maxdate获取对应日期的金额，第二次关联lastdate获取前一天的金额。
3. 使用LEFT JOIN确保即使table2中没有对应日期的记录，也能返回该id的日期信息（金额字段会显示为NULL）。

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