Oracle SQL：如何筛选存在多个操作记录的员工数据

我想要筛选出存在多个操作记录的员工（如示例中的员工2），尝试使用GROUP BY和HAVING子句但返回空结果，该如何正确实现需求？

原SQL语句 #

select xx.* from (SELECT DISTINCT
        papf.person_id,
        papf.person_number                      ,
        ppnf.display_name                       ,
        pgft.name  grade                        ,
        pjft.name  job                          ,
        hapft.name position                     ,
        national_identifier_number "national id",
        to_char(papf.start_date,'dd/mm/yyyy') "Hire Date",
        case when paaf.assignment_status_type ='ACTIVE' then 'Active' else 'Inactive' end   Status,
       to_char(paaf.effective_start_date,'dd/mm/yyyy') "Effective Period2",
       pat.action_name Action       
        
FROM
                                                                                             
        per_all_people_f papf
        
        LEFT JOIN per_person_names_f ppnf ON         ppnf.person_id        = papf.person_id
        AND     ppnf.name_type = 'GLOBAL'
        AND     SYSDATE BETWEEN ppnf.effective_start_date AND     ppnf.effective_end_date
        
        LEFT JOIN per_all_assignments_m paaf ON paaf.person_id = papf.person_id
                AND paaf.assignment_type = 'E'

        LEFT JOIN       HR_ALL_POSITIONS_F_TL hapft on hapft.position_id = paaf.position_id
        AND hapft.language    = 'US'
        
        LEFT JOIN       per_jobs_f_tl pjft ON pjft.job_id   = paaf.job_id
        AND     pjft.language = 'US'


        LEFT JOIN       per_grades_f_tl pgft ON      pgft.grade_id = paaf.grade_id
        AND     pgft.language = 'US'

        LEFT JOIN       PER_NATIONAL_IDENTIFIERS pna ON pna.person_id            = papf.person_id
        
        LEFT JOIN PER_ACTION_OCCURRENCES pao ON pao.action_occurrence_id = paaf.action_occurrence_id 
        
        
        LEFT JOIN PER_ACTIONS_TL pat ON pat.action_id = pao.action_id 
        and pat.language = 'US'
        
        group by  
        papf.person_number,
        papf.person_id,
        ppnf.display_name,
        pgft.name,
        pjft.name,
        hapft.name,
        national_identifier_number,
        to_char(papf.start_date,'dd/mm/yyyy'),
       to_char(paaf.effective_start_date,'dd/mm/yyyy'),
       pat.action_name,
       paaf.assignment_status_type
       
       HAVING COUNT(person_number)>1

)XX
where XX.person_id IN
(NVL (:Emps, XX.person_id)) 
 and (XX.Status in :P_status or :P_status is null)
ORDER BY XX.person_number

示例数据 #

问题原因 #

你当前的GROUP BY子句包含了action_name、Effective Period2等和操作记录强相关的字段，这会导致每个不同的操作记录都被分成单独的分组，每个分组的COUNT(person_number)结果都是1，自然触发不了HAVING COUNT(person_number)>1的条件。

解决方案 #

正确的思路是先筛选出有多个操作记录的员工ID，再关联主查询获取详细信息，具体修改如下：

SELECT xx.*
FROM (
    -- 主查询保留原有关联逻辑，获取员工所有详细信息
    SELECT DISTINCT
        papf.person_id,
        papf.person_number,
        ppnf.display_name,
        pgft.name grade,
        pjft.name job,
        hapft.name position,
        national_identifier_number "national id",
        to_char(papf.start_date,'dd/mm/yyyy') "Hire Date",
        CASE WHEN paaf.assignment_status_type ='ACTIVE' THEN 'Active' ELSE 'Inactive' END Status,
        to_char(paaf.effective_start_date,'dd/mm/yyyy') "Effective Period2",
        pat.action_name Action
    FROM per_all_people_f papf
    LEFT JOIN per_person_names_f ppnf 
        ON ppnf.person_id = papf.person_id
        AND ppnf.name_type = 'GLOBAL'
        AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
    LEFT JOIN per_all_assignments_m paaf 
        ON paaf.person_id = papf.person_id
        AND paaf.assignment_type = 'E'
    LEFT JOIN HR_ALL_POSITIONS_F_TL hapft 
        ON hapft.position_id = paaf.position_id
        AND hapft.language = 'US'
    LEFT JOIN per_jobs_f_tl pjft 
        ON pjft.job_id = paaf.job_id
        AND pjft.language = 'US'
    LEFT JOIN per_grades_f_tl pgft 
        ON pgft.grade_id = paaf.grade_id
        AND pgft.language = 'US'
    LEFT JOIN PER_NATIONAL_IDENTIFIERS pna 
        ON pna.person_id = papf.person_id
    LEFT JOIN PER_ACTION_OCCURRENCES pao 
        ON pao.action_occurrence_id = paaf.action_occurrence_id 
    LEFT JOIN PER_ACTIONS_TL pat 
        ON pat.action_id = pao.action_id 
        AND pat.language = 'US'
) xx
-- 关联子查询，筛选出有多个操作记录的员工
INNER JOIN (
    SELECT paaf.person_id
    FROM per_all_assignments_m paaf
    JOIN PER_ACTION_OCCURRENCES pao 
        ON pao.action_occurrence_id = paaf.action_occurrence_id
    JOIN PER_ACTIONS_TL pat 
        ON pat.action_id = pao.action_id
        AND pat.language = 'US'
    WHERE paaf.assignment_type = 'E'
    GROUP BY paaf.person_id
    HAVING COUNT(DISTINCT pao.action_occurrence_id) > 1 -- 统计不同的操作记录数量
) multi_action_emps ON xx.person_id = multi_action_emps.person_id
WHERE xx.person_id IN (NVL (:Emps, xx.person_id)) 
  AND (xx.Status IN :P_status OR :P_status IS NULL)
ORDER BY xx.person_number

关键修改点 #

1. 新增子查询multi_action_emps：仅针对员工ID分组，统计其关联的操作记录数量（用COUNT(DISTINCT pao.action_occurrence_id)确保每个操作被单独计数），筛选出数量大于1的员工ID。
2. 主查询通过INNER JOIN关联这个子查询，只保留有多个操作记录的员工数据。
3. 移除了原主查询中的GROUP BY和HAVING子句，避免因过多分组字段导致的统计错误。

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