查询为同一员工返回多行结果的问题排查与修正

原始数据表数据：

EE   ELEMENT_NAME     RESULT_VALUE   EFFECTIVE DATE   INPUT VALUE
-----------------------------------------------------------------
12   Overtime           1000           10-APR-2023      earning
12   Overtime           10             10-APR-2023      Hours
12   REGULAR_RETRO      110            10-APR-2023      earning
11   REGULAR_RETRO      120            10-apr-2023      earning         

期望输出：

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
----------------------------------------------------------------
12   110                1000                        10  
11   120                

当前使用的SQL查询存在问题，会为员工12返回两行结果，不符合单行展示需求：

SELECT
    ee person_number,
    SUM(CASE 
            WHEN peen.element_name IN 'REGULAR RETRO'
                THEN (RESULT_VALUE) 
        END) REGULAR_RETRO,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value = 'Hours'
                THEN (RESULT_VALUE)
        END) OVERTIME_hours,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value =  'earning'
                THEN (RESULT_VALUE) 
        END) OVERTIME_paid 
FROM
    PER_ALL_PEOPLE_F PAPF
    PER_ELEMENT_NAME PEEN,
    PEr_ELEMENT_TYPE PET
WHERE
    PAPF.PERSON_ID = PEEN.PERSON_ID
    AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
    AND PET.INPUT_VALUE IN ('Earning','Hours')
    AND peen.element_name IN ('REGULAR RETRO', 'OVERTIME')
GROUP BY
    person_number

SELECT
    PAPF.person_number AS EE,
    SUM(CASE 
            WHEN PEEN.element_name = 'REGULAR_RETRO' THEN RESULT_VALUE
        END) AS REGULAR_RETRO,
    SUM(CASE 
            WHEN PEEN.element_name = 'OVERTIME' AND PEEN.input_value = 'earning' THEN RESULT_VALUE
        END) AS OVERTIME_PAID,
    SUM(CASE 
            WHEN PEEN.element_name = 'OVERTIME' AND PEEN.input_value = 'Hours' THEN RESULT_VALUE
        END) AS OVERTIME_Hours_taken
FROM
    PER_ALL_PEOPLE_F PAPF
JOIN PER_ELEMENT_NAME PEEN ON PAPF.PERSON_ID = PEEN.PERSON_ID
JOIN PER_ELEMENT_TYPE PET ON PEEN.ELEMENT_ID = PET.ELEMENT_ID
WHERE
    PET.INPUT_VALUE IN ('Earning','Hours')
    AND PEEN.element_name IN ('REGULAR_RETRO', 'OVERTIME')
GROUP BY
    PAPF.person_number

1. 语法错误修正：原查询中IN 'REGULAR RETRO'写法错误，IN操作符后需跟括号包裹的列表，单个值直接用=更简洁。
2. 避免笛卡尔积：原查询用逗号连接表属于旧版隐式连接写法，易因连接条件遗漏产生笛卡尔积，改用显式JOIN语法确保连接逻辑清晰正确。
3. 列名与分组一致性：原查询SELECT的ee person_number列名不明确，修正为PAPF.person_number AS EE匹配原始数据列名，同时GROUP BY使用明确的表别名列，避免分组逻辑混乱。
4. 列名匹配期望输出：调整输出列名与期望结果的OVERTIME_PAID、OVERTIME_Hours_taken保持一致。

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