如何在DEAL与DEPTH表左连接时取符合条件的最大depth_end_date行？

这个需求很常见，我来给你几个实用的解决方案，适配不同的数据库版本和场景：

方案1：用窗口函数ROW_NUMBER()（推荐，通用型） #

这是现代数据库（比如MySQL 8+、PostgreSQL、SQL Server）最常用的方法，逻辑清晰且易维护：

SELECT d.*, filtered_depth.*
FROM deal d
LEFT JOIN (
    SELECT 
        depth.*,
        -- 按部门分组，结束日期倒序排，给每条记录打行号
        ROW_NUMBER() OVER (
            PARTITION BY depth_gid 
            ORDER BY depth_end_date DESC
        ) AS rn
    FROM depth
) filtered_depth 
ON (
    d.dept_gid = filtered_depth.depth_gid 
    AND d.deal_begin_date <= filtered_depth.depth_end_date 
    AND d.deal_end_date >= filtered_depth.depth_start_date
)
-- 只保留每个部门下结束日期最大的行，同时保留DEPTH无匹配的DEAL记录
WHERE filtered_depth.rn = 1 OR filtered_depth.rn IS NULL;

关键点说明： #

• PARTITION BY depth_gid：把DEPTH表按部门ID分组，确保我们只在同一个部门内找最大的结束日期
• ORDER BY depth_end_date DESC：让每个组内结束日期最晚的记录排第一，行号为1
• WHERE子句的OR filtered_depth.rn IS NULL：必须加上，否则会丢失那些在DEPTH里没有匹配记录的DEAL行（左连接的核心需求）

如果同一个部门下有多个记录的depth_end_date完全相同且都是最大值，ROW_NUMBER()会随机选一条。如果需要固定结果，可以在ORDER BY里加额外字段，比如depth_start_date DESC或者主键ID，比如：

ORDER BY depth_end_date DESC, depth_id DESC

方案2：子查询+MAX()（适配老版本数据库） #

如果你用的是不支持窗口函数的老数据库（比如MySQL 5.x），可以用子查询先找到每个部门的最大结束日期，再关联回DEPTH表拿完整记录：

SELECT d.*, depth.*
FROM deal d
LEFT JOIN (
    -- 第一步：找到每个部门下和DEAL日期相交的最大结束日期
    SELECT 
        depth_gid,
        MAX(depth_end_date) AS max_end_date
    FROM depth
    WHERE EXISTS (
        SELECT 1 
        FROM deal d2 
        WHERE d2.dept_gid = depth.depth_gid 
        AND d2.deal_begin_date <= depth.depth_end_date 
        AND d2.deal_end_date >= depth.depth_start_date
    )
    GROUP BY depth_gid
) max_depth 
ON d.dept_gid = max_depth.dept_gid
-- 第二步：关联回DEPTH表，拿到对应最大结束日期的完整记录
LEFT JOIN depth 
ON (
    depth.depth_gid = max_depth.dept_gid 
    AND depth.depth_end_date = max_depth.max_end_date
    AND d.deal_begin_date <= depth.depth_end_date 
    AND d.deal_end_date >= depth.depth_start_date
);

注意： #

如果同一个部门下有多个记录的depth_end_date等于最大值，这个方法会返回多条结果。如果要避免，需要在子查询里再加筛选条件（比如同时取最大的depth_start_date）。

方案3：LATERAL JOIN（简洁高效，适配支持的数据库） #

如果你的数据库支持LATERAL JOIN（比如PostgreSQL、MySQL 8+），这种写法更直观，直接给每个DEAL记录匹配符合条件的DEPTH行中结束日期最晚的那条：

SELECT d.*, depth.*
FROM deal d
LEFT JOIN LATERAL (
    SELECT *
    FROM depth
    WHERE 
        depth.depth_gid = d.dept_gid 
        AND d.deal_begin_date <= depth.depth_end_date 
        AND d.deal_end_date >= depth.depth_start_date
    ORDER BY depth_end_date DESC
    LIMIT 1 -- 只取结束日期最晚的一条
) depth ON true;

这个方法的优势是逻辑非常直接，相当于给每个DEAL行单独做一次小查询，取符合条件的TOP1记录，性能也不错（如果DEPTH表的depth_gid、depth_end_date有索引的话）。

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