MySQL多表查询：关联ABCD表并获取D表最新日期记录的实现方案

DDL & DML 示例 #

CREATE TABLE a (
  `ID` VARCHAR(8), `yearly_return` DECIMAL(16,2)
);
INSERT INTO a
VALUES
  ('0001', 0.4), ('0002', 0);
CREATE TABLE b (
  `ID` VARCHAR(8), `drawdown` DECIMAL(16,2)
);
INSERT INTO b
VALUES
  ('0001', 0.2), ('0002', 0);
CREATE TABLE c (
  `ID` VARCHAR(8), `author` VARCHAR(255)
);
INSERT INTO c
VALUES
  ('0001', 'join'), ('0002', '');
CREATE TABLE d (
  `ID` VARCHAR(8), `date` VARCHAR(20), `yield_the_day` DECIMAL(16,2)
);
INSERT INTO d
VALUES
  ('0001','20220910', 0.02), ('0001','20220911', 0.06);

环境 #

MySQL 8.0.26

背景 #

• 表a、b、c的主键为ID（如0001）
• 表d的主键为ID与date组成的联合主键（如0001、20220912）

需求 #

关联表a、b、c、d得到结果集e，同时获取表d中对应ID的最新日期记录。

已尝试SQL #

SELECT
    a.yearly_return,
    b.drawdown,
    c.author
    
FROM
    a
    INNER JOIN b ON a.id = b.id
    INNER JOIN c ON a.id = c.id

解决方案 #

方法1：使用窗口函数（推荐，MySQL 8.0+支持） #

利用ROW_NUMBER()窗口函数标记每个ID的最新记录，再关联其他表：

SELECT
    a.yearly_return,
    b.drawdown,
    c.author,
    d_latest.date,
    d_latest.yield_the_day
FROM
    a
    INNER JOIN b ON a.ID = b.ID
    INNER JOIN c ON a.ID = c.ID
    LEFT JOIN (
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date DESC) AS rn
        FROM d
    ) d_latest ON a.ID = d_latest.ID AND d_latest.rn = 1;

• 逻辑说明：子查询中按ID分组，对每组内的记录按date倒序编号，最新记录的编号为1；通过LEFT JOIN关联，保留所有a、b、c的数据（若只需保留有d记录的行，替换为INNER JOIN即可）。

方法2：关联子查询（兼容低版本MySQL） #

通过子查询获取每个ID的最大日期，再匹配表d的对应记录：

SELECT
    a.yearly_return,
    b.drawdown,
    c.author,
    d.date,
    d.yield_the_day
FROM
    a
    INNER JOIN b ON a.ID = b.ID
    INNER JOIN c ON a.ID = c.ID
    LEFT JOIN d ON a.ID = d.ID
        AND d.date = (SELECT MAX(date) FROM d WHERE ID = a.ID);

• 逻辑说明：子查询(SELECT MAX(date) FROM d WHERE ID = a.ID)得到当前ID的最新日期，以此为条件关联表d，获取对应记录。

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