基于两张MySQL表获取最早Val1并与Val2匹配生成状态的SQL查询需求

嘿，咱们一步步来实现你要的SQL查询，拆解下来分两个核心步骤：先从Table1里捞每个ID+key组合的最早记录，再和Table2关联生成状态字段。

步骤1：获取Table1中每个ID+key的最早Date1对应的Val1 #

首先得把Table1里每个分组（ID+key）中Date1最小的那条记录筛选出来，这里用子查询先找到每个分组的最小日期，再关联回原表拿到对应的Val1和Date1：

SELECT 
    t1.ID,
    t1.`key`,
    t1.Val1,
    t1.Date1
FROM Table1 t1
INNER JOIN (
    SELECT 
        ID,
        `key`,
        MIN(Date1) AS min_date1
    FROM Table1
    GROUP BY ID, `key`
) t1_min ON t1.ID = t1_min.ID 
        AND t1.`key` = t1_min.`key` 
        AND t1.Date1 = t1_min.min_date1

步骤2：关联Table2并生成Status字段 #

把上面的结果作为临时表，和Table2通过ID和key关联，然后用CASE判断Val1和Val2是否相等，生成Status字段：

完整的SQL查询如下：

SELECT 
    t1_filtered.ID,
    t1_filtered.`key`,
    t1_filtered.Val1,
    t2.Val2,
    t1_filtered.Date1,
    t2.Date2,
    CASE WHEN t1_filtered.Val1 = t2.Val2 THEN TRUE ELSE FALSE END AS Status
FROM (
    -- 子查询：获取Table1每个ID+key组合中Date1最早的记录
    SELECT 
        t1.ID,
        t1.`key`,
        t1.Val1,
        t1.Date1
    FROM Table1 t1
    INNER JOIN (
        SELECT 
            ID,
            `key`,
            MIN(Date1) AS min_date1
        FROM Table1
        GROUP BY ID, `key`
    ) t1_min ON t1.ID = t1_min.ID 
            AND t1.`key` = t1_min.`key` 
            AND t1.Date1 = t1_min.min_date1
) t1_filtered
LEFT JOIN Table2 t2 ON t1_filtered.ID = t2.ID AND t1_filtered.`key` = t2.`key`
-- 注：如果要求必须匹配Table2的记录，把LEFT JOIN改成INNER JOIN即可

验证结果 #

代入你提供的测试数据，运行这个查询会得到你期望的输出：

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