Oracle SQL：员工表多记录合并及查询性能优化问询

我来帮你搞定这个问题——既要严格符合你的业务规则返回单条数据，又要把慢到让人崩溃的查询速度提上来。先把业务规则再明确一遍，确保我们对齐：

业务规则回顾 #

• 单条记录的EMPID：直接返回原数据
• 多条记录的EMPID：
  1. 选取StartDate <= sysdate的所有记录里，StartDate最新的那条作为基础记录（除EndDate外的字段用这条）
  2. EndDate优先取该EMPID下EndDate > sysdate 或 EndDate为null的值；如果没有这类值，就取该EMPID下最新的EndDate

原查询的性能问题分析 #

你原来的查询嵌套了多层子查询，还用到了关联子查询（就是那个(select t.enddate from ... where t2.empid=t1.empid)部分）——这种写法在大数据量下会触发N次子查询（N是主查询的行数），相当于把表扫了N遍，这就是为什么3万条数据要9分钟的核心原因。

优化后的SQL #

我们用CTE（公共表表达式）把逻辑拆成清晰的两步，同时用窗口函数和聚合一次性完成计算，避免多次扫描表：

WITH emp_target_enddate AS (
    -- 预先计算每个EMPID的目标EndDate：优先取有效（未来/无结束）的EndDate，否则取最新的EndDate
    SELECT 
        EMPID,
        COALESCE(
            MAX(CASE WHEN EndDate IS NULL OR EndDate > SYSDATE THEN EndDate END),
            MAX(EndDate)
        ) AS target_enddate
    FROM employee
    GROUP BY EMPID
),
emp_ranked_records AS (
    -- 给每个EMPID的记录排序：StartDate<=sysdate的排在前面，按StartDate倒序；StartDate>sysdate的排后面
    SELECT 
        t.*,
        COUNT(*) OVER (PARTITION BY EMPID) AS emp_record_count,
        ROW_NUMBER() OVER (
            PARTITION BY EMPID 
            ORDER BY CASE WHEN StartDate <= SYSDATE THEN StartDate ELSE TO_DATE('0001-01-01','YYYY-MM-DD') END DESC
        ) AS record_rank
    FROM employee t
)
-- 最终筛选：单条记录直接返回，多条记录取排名第一且符合StartDate<=sysdate的记录，替换EndDate
SELECT 
    err.EMPID,
    err.PID,
    err.Name,
    err.StartDate,
    CASE 
        WHEN err.emp_record_count = 1 THEN err.EndDate
        ELSE ete.target_enddate
    END AS EndDate
    -- 这里替换成你需要的其他字段，尽量不要用SELECT *
    -- , err.OtherField1, err.OtherField2...
FROM emp_ranked_records err
JOIN emp_target_enddate ete ON err.EMPID = ete.EMPID
WHERE 
    -- 单条记录直接保留，多条记录取排名第一的有效记录
    (err.emp_record_count = 1) 
    OR (err.emp_record_count > 1 AND err.record_rank = 1);

性能优化的额外建议 #

光改SQL还不够，配合索引能让速度再上一个台阶：

1. 创建复合索引加速排序和分组：

   CREATE INDEX idx_emp_empid_startdate ON employee(EMPID, StartDate DESC);
   

   这个索引会让窗口函数的PARTITION BY EMPID ORDER BY StartDate操作几乎瞬间完成。
2. 创建索引加速EndDate的聚合计算：

   CREATE INDEX idx_emp_empid_enddate ON employee(EMPID, EndDate DESC);
   

3. 不要用SELECT *，只选取你需要的字段——减少数据传输量和内存占用，尤其是大表的时候效果明显。
4. 更新表统计信息，让Oracle优化器生成最优执行计划：

   EXEC DBMS_STATS.GATHER_TABLE_STATS('你的模式名', 'employee');
   

验证结果 #

用你给出的测试数据（假设sysdate在2020年8月，此时第二条217121的StartDate还未生效），这个查询会返回你预期的结果：

217121 761331 Tefan 21-FEB-19 null
602315 764321 Wolf 01-DEC-15 null
766470 766472 Deva 14-JUL-20 31-DEC-22

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