多表关联查询：高效获取最新唯一记录的SQL方案求助

针对大数据量表的查询需求，核心是通过窗口函数+索引优化避免重复并保证性能，以下是具体实现：

核心思路 #

用ROW_NUMBER()窗口函数按员工分区，按项目起始日期倒序排序，仅保留每个分区的第一条记录（即最新项目），从根源避免重复；同时通过合理索引减少大表全扫描的开销。

适配需求的表结构假设 #

根据你的期望结果和模型，假设涉及3张核心表：

• employees: emp_id（主键）、emp_name
• projects: proj_id（主键）、proj_desc、start_date、company_id、emp_id（关联员工）
• companies: company_id（主键）、company_name

最优SQL查询 #

WITH ranked_projects AS (
    SELECT
        e.emp_id,
        e.emp_name,
        p.proj_desc AS "Description",
        TO_CHAR(p.start_date, 'DD/Mon/YYYY') AS start_date,
        c.company_name,
        -- 按员工分区，按项目日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY e.emp_id ORDER BY p.start_date DESC) AS rn
    FROM employees e
    JOIN projects p ON e.emp_id = p.emp_id
    JOIN companies c ON p.company_id = c.company_id
    -- 仅需特定员工时提前过滤，减少处理数据量
    WHERE e.emp_name IN ('JON', 'Alfred')
)
SELECT emp_id, emp_name, "Description", start_date, company_name
FROM ranked_projects
WHERE rn = 1;

性能优化要点 #

• 添加联合索引：给projects(emp_id, start_date, company_id)创建联合索引，让窗口排序和关联查询直接走索引，避免全表扫描；
• 提前过滤数据：如果只需要特定员工（如JON和Alfred），在CTE的WHERE子句中提前过滤，减少后续处理的数据量；
• 校验关联逻辑：确认表关联字段的唯一性，避免因关联条件宽松产生笛卡尔积导致重复。

重复问题的根源 #

你之前的查询大概率未做分区排序过滤：要么是关联时产生了一对多的重复行，要么是没指定“取最新记录”的逻辑，导致同一员工的多条项目记录都被返回。用ROW_NUMBER()分区后取rn=1，能确保每个员工仅返回最新的那条记录。

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