如何筛选选定行的max()值？document_files表active=0仅取最新行的查询实现

可以通过窗口函数或者低版本兼容的子查询方案实现，均无需对全列做group by，性能表现优异：

方案一：窗口函数实现（推荐，支持MySQL 8.0+/PostgreSQL/SQL Server等所有现代数据库） #

核心逻辑是给每个文档下的document_files行按时间倒序打行号，直接过滤出符合要求的行即可：

select *
from documents d
inner join (
    select *,
        row_number() over(
            partition by document_id 
            order by last_stamp desc
        ) as rn
    from document_files
    where active in (0, 1)
) df on df.document_id = d.id
where 
    -- 保留所有active=1的行
    df.active = 1 
    -- active=0的行只保留每个文档时间最新的第一条
    or (df.active = 0 and df.rn = 1);

方案二：旧版本数据库兼容方案（适用于MySQL 5.x等不支持窗口函数的场景） #

通过union all拼接两类符合要求的行，仅需要对定位行的关键字段做分组，无需处理全列：

-- 先取所有active=1的行
select *
from documents d
inner join document_files df on df.document_id = d.id
where df.active = 1

union all

-- 再取每个文档active=0的最新行
select *
from documents d
inner join document_files df on df.document_id = d.id
where df.active = 0
and (df.document_id, df.last_stamp) in (
    select document_id, max(last_stamp)
    from document_files
    where active = 0
    group by document_id
);

优化建议 #

给document_files表添加联合索引 (document_id, active, last_stamp)，可以大幅降低上述两个查询的IO开销，提升查询速度。

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