SQL查询求助：无法获取job_history表Top2高频job_id及对应平均薪资

你的查询存在两个核心问题： #

• 统计逻辑偏差：直接关联employees和job_history后统计的次数，并非job_history表中job_id的独立出现频率，而是两张表关联后匹配记录的数量，这会导致频率统计不准确。
• 筛选范围错误：>= all(...)仅能获取出现次数等于最大值的job_id，无法覆盖出现频率Top2的结果——如果第二高频的job_id次数低于最大值，会直接被过滤掉。

分步修正方案 #

第一步：获取job_history中出现频率最高的两个job_id #

根据是否允许并列结果，分两种写法：

1. 严格取前2条（即使有并列也只保留两个）：

SELECT job_id
FROM (
    SELECT job_id,
           ROW_NUMBER() OVER(ORDER BY COUNT(job_id) DESC) AS rank_num
    FROM HR.job_history
    GROUP BY job_id
) ranked_jobs
WHERE rank_num <= 2;

2. 允许并列结果（比如两个job_id出现次数相同且都是最高，会全部保留，结果可能多于2条）：

SELECT job_id
FROM (
    SELECT job_id,
           RANK() OVER(ORDER BY COUNT(job_id) DESC) AS rank_num
    FROM HR.job_history
    GROUP BY job_id
) ranked_jobs
WHERE rank_num <= 2;

第二步：关联Employees表计算对应平均薪资 #

将第一步的结果作为子查询，关联employees表统计薪资：

SELECT e.job_id, AVG(e.salary) AS average_salary
FROM HR.employees e
JOIN (
    -- 替换为第一步的子查询代码
    SELECT job_id
    FROM (
        SELECT job_id,
               ROW_NUMBER() OVER(ORDER BY COUNT(job_id) DESC) AS rank_num
        FROM HR.job_history
        GROUP BY job_id
    ) ranked_jobs
    WHERE rank_num <= 2
) top_jobs ON e.job_id = top_jobs.job_id
GROUP BY e.job_id;

修正逻辑说明 #

1. 先单独统计job_history中每个job_id的出现频率，通过窗口函数排序筛选出Top2，确保拿到准确的目标job_id集合。
2. 再用筛选后的job_id关联employees表，避免直接关联导致的统计偏差，精准计算对应岗位的平均薪资。

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