MySQL查询问题：如何仅显示重复出现的job_processing_id

你的SQL结果里仍出现单次ID，核心原因是子查询统计的是全表的重复job_processing_id，但主查询加了u.id NOT IN (7652, 5225)的过滤条件——有些原本全表重复的ID，经过用户过滤后只剩下一条符合条件的记录，就会看起来像“单次出现”。另外你的SQL最后还有语法错误：and added_a不完整，需要补充完整的added_at条件（比如added_at >= 'xxxx-xx-xx'）。

下面是两种修正方案：

方案一：让子查询和主查询共享过滤条件 #

把u.id NOT IN (7652, 5225)的过滤逻辑加到子查询里，确保子查询只统计符合用户过滤条件后仍重复的job_processing_id：

SELECT 
    cdh.job_processing_id, 
    cdh.test_counter, 
    tt.test_type, 
    cd.description, 
    u.name, 
    cdh.added_at 
FROM call_description_history AS cdh 
LEFT JOIN test_type AS tt ON tt.id = cdh.test_type_id 
LEFT JOIN call_description AS cd ON cd.id = cdh.call_description_id 
LEFT JOIN user AS u ON u.id = cdh.added_by 
WHERE 
    job_processing_id IN (
        SELECT job_processing_id 
        FROM call_description_history AS cdh_sub
        LEFT JOIN user AS u_sub ON u_sub.id = cdh_sub.added_by
        WHERE u_sub.id NOT IN (7652, 5225)
        GROUP BY job_processing_id 
        HAVING COUNT(job_processing_id) > 1
    ) 
    AND u.id NOT IN (7652, 5225)
    -- 补充完整你的added_at条件，比如：AND cdh.added_at >= '2023-01-01'

方案二：用窗口函数直接计算过滤后记录的重复次数 #

这种方法更直观，一次性计算每个job_processing_id在过滤后的结果集中的出现次数，直接筛选次数>1的记录：

SELECT 
    job_processing_id, 
    test_counter, 
    test_type, 
    description, 
    name, 
    added_at 
FROM (
    SELECT 
        cdh.job_processing_id, 
        cdh.test_counter, 
        tt.test_type, 
        cd.description, 
        u.name, 
        cdh.added_at,
        COUNT(*) OVER (PARTITION BY cdh.job_processing_id) AS id_count
    FROM call_description_history AS cdh 
    LEFT JOIN test_type AS tt ON tt.id = cdh.test_type_id 
    LEFT JOIN call_description AS cd ON cd.id = cdh.call_description_id 
    LEFT JOIN user AS u ON u.id = cdh.added_by 
    WHERE 
        u.id NOT IN (7652, 5225)
        -- 补充完整你的added_at条件
) AS filtered_data
WHERE id_count > 1

这两种方案都能确保最终结果里的job_processing_id，在符合u.id NOT IN (7652, 5225)和你补充的added_at条件后，仍然是重复出现的。

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