Oracle SQL如何合并两个独立查询得到duration和measure两列结果

你遇到的「单行子查询返回多行」错误核心是两个问题：

1. 第二个统计MEASURE的查询没有按person_id分组，无法和单个人员一一对应
2. 嵌套关联前没有保证两个查询结果的person_id唯一，关联时触发多行匹配

完整实现代码 #

SELECT 
    abs_stat.person_id,
    abs_stat.total_duration,
    NVL(time_stat.total_measure, 0) AS total_measure -- 无MEASURE数据时默认返回0，不需要可直接取time_stat.total_measure
FROM (
    -- 统计DURATION的子查询，按person_id预聚合保证唯一
    SELECT 
        SUM(apae.duration) AS total_duration,
        pps.person_id
    FROM 
        anc_per_abs_entries apae,
        anc_absence_types_vl abs,
        per_periods_of_service pps,
        anc_absence_type_reasons_f abtype,
        anc_absence_reasons_f_tl ab_reason
    WHERE 1=1
        AND TRUNC(apae.start_date) BETWEEN TRUNC(:From_Date) AND TRUNC(:To_Date)
        AND apae.APPROVAL_STATUS_CD = 'APPROVED'
        AND apae.ABSENCE_STATUS_CD = 'SUBMITTED'
        AND abs.name LIKE 'Paid Personal Leave' 
        AND apae.period_of_service_id = pps.period_of_service_id
        AND apae.absence_type_id = abs.absence_type_id
        AND abtype.absence_type_id = abs.absence_type_id
        AND ab_reason.absence_reason_id = abtype.absence_reason_id
        AND apae.absence_type_reason_id = abtype.absence_type_reason_id
    GROUP BY pps.person_id
) abs_stat
LEFT JOIN (
    -- 统计MEASURE的子查询，补充按person_id分组保证唯一
    SELECT 
        SUM(rec.measure) AS total_measure,
        rec.person_id
    FROM
        time_track rec,
        time_type rec_type,
        per_all_people_f papf
    WHERE 1=1
        AND rec_type.tm_bldg_blk_version = rec.tm_rec_version
        AND rec.tm_rec_type IN ('RANGE', 'MEASURE')
        AND TRUNC(rec.START_TIME) BETWEEN TRUNC(:From_Date) AND TRUNC(:To_Date)
        AND papf.person_id = rec.person_id
    GROUP BY rec.person_id
) time_stat
ON abs_stat.person_id = time_stat.person_id

逻辑说明 #

• 两个子查询先单独完成聚合计算，各自保证结果内person_id唯一，从根源避免关联时的多行匹配问题
• 以DURATION统计结果为左表做左连接，完全满足「无MEASURE数据的人员也保留在结果集」的需求

优化提示 #

你原SQL中to_date(To_Char(日期,'YYYY-MM-DD'),'YYYY-MM-DD')的写法完全冗余，直接用TRUNC(日期)即可，执行效率更高，也不会导致日期字段的索引失效。另外你原第一个查询中多余写了AND pps.person_id = papf.person_id的条件，但第一个查询的FROM子句没有引入papf表，已在上述代码中删除，避免执行报错。

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