如何关联两张表并按主表ID获取最新datetime记录？

API 探路者

2026-7-30

需求说明

需要关联两张表，从第二张表中获取每个主表（第一张表）ID对应的最新date记录，且每个主表ID仅返回一条结果。

表结构与数据

主表（命名为`terms`）

id	title
1	a
2	ac
3	at

关联表（命名为`term_contents`）

id	content	author	date	fk_term
202	Maecenas ut massa ...	Renee Copnar	2022-08-17	1
351	Sociis natoque penatibus...	Rhona Levesque	2022-10-30	1
557	Phasellus in felis...	Kissie Blabber	2022-09-11	1
383	Maecenas tristique...	Saunders Robet	2022-08-18	2
429	In sagittis dui...	Jorey Strodder	2022-12-21	2
655	Maecenas ut massa...	Sim Harrod	2023-01-07	2

期望输出

每个主表ID仅返回一条最新记录：

content	author	date	fk_term	title
Sociis natoque penatibus...	Rhona Levesque	2022-10-30	1	a
Maecenas ut massa...	Sim Harrod	2023-01-07	2	ac

解决方案

方法1：窗口函数`ROW_NUMBER()`（推荐）

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库，逻辑清晰易维护：

SELECT tc.content, tc.author, tc.date, tc.fk_term, t.title
FROM (
    SELECT 
        *,
        -- 按主表ID分组，每组内按日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY fk_term ORDER BY date DESC) AS rn
    FROM term_contents
) tc
JOIN terms t ON tc.fk_term = t.id
-- 筛选每组排名第一的记录（最新日期）
WHERE tc.rn = 1;

方法2：关联子查询

针对不支持窗口函数的旧版数据库（如MySQL 5.x），可以用该写法：

SELECT tc.content, tc.author, tc.date, tc.fk_term, t.title
FROM term_contents tc
JOIN terms t ON tc.fk_term = t.id
-- 匹配当前主表ID对应的最新日期记录
WHERE tc.date = (
    SELECT MAX(date) 
    FROM term_contents 
    WHERE fk_term = tc.fk_term
);