SQL查询：统计入职Oracle前曾在IBM任职的成员数量

核心判断规则：同一member_id下，只要存在IBM的入职年份早于Oracle的入职年份，该成员就算符合统计条件，和两次任职中间是否跳槽其他公司无关。
你之前用CASE WHEN没找到方向的原因很简单：这个判断需要对比同一个成员下不同行的时间值，属于跨行列的组内比较，单行级别的CASE WHEN没法直接完成跨记录判断，用以下两种常规思路都可以实现： #

思路1：自关联写法（全SQL版本兼容） #

步骤拆分：

• 先分别筛选出所有Oracle、IBM的任职记录，拆分出两个子数据集
• 按member_id关联两个数据集，保留IBM入职年份早于Oracle入职年份的匹配记录
• 对匹配到的member_id去重后计数，就是最终结果
  参考代码：

SELECT COUNT(DISTINCT o.member_id) AS total_qualified_member
FROM (
    -- 筛出所有Oracle任职记录
    SELECT member_id, Year_started AS oracle_start_year
    FROM work_history -- 替换成你的实际表名
    WHERE company = 'Oracle'
) o
INNER JOIN (
    -- 筛出所有IBM任职记录
    SELECT member_id, Year_started AS ibm_start_year
    FROM work_history -- 替换成你的实际表名
    WHERE company = 'IBM'
) i
ON o.member_id = i.member_id
WHERE i.ibm_start_year < o.oracle_start_year;

思路2：窗口函数写法（支持MySQL8+、PostgreSQL、Hive等主流环境） #

步骤拆分：

• 用窗口函数按member_id分组，计算出每个成员入职Oracle的年份，附加到该成员的每一条任职记录上
• 筛选出公司为IBM、且IBM入职年份早于同组Oracle入职年份的记录
• 对匹配到的member_id去重计数即可
  参考代码：

SELECT COUNT(DISTINCT member_id) AS total_qualified_member
FROM (
    SELECT 
        member_id,
        company,
        Year_started,
        -- 取当前成员最早的Oracle入职年份
        MIN(CASE WHEN company = 'Oracle' THEN Year_started END) 
            OVER (PARTITION BY member_id) AS oracle_entry_year
    FROM work_history -- 替换成你的实际表名
) t
WHERE company = 'IBM'
  AND Year_started < oracle_entry_year;

基于你给出的示例数据运行上述代码，返回结果为2，和手动统计的预期结果一致。

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