如何用MySQL提取同一key同时包含a和b两种status的行

方法一：子查询 + GROUP BY + HAVING #

这是最直接的思路，先筛选出符合条件的key，再取出这些key对应的所有行：

SELECT t.id, t.`key`, t.status
FROM your_table t
WHERE t.`key` IN (
    -- 找出同时拥有status='a'和'status='b'的key
    SELECT `key`
    FROM your_table
    WHERE status IN ('a', 'b')
    GROUP BY `key`
    HAVING COUNT(DISTINCT status) = 2
)
ORDER BY t.`key`, t.status;

• 子查询部分：仅关注status为'a'或'b'的行，按key分组后，用HAVING COUNT(DISTINCT status) = 2确保该key同时存在两种状态。
• 外层查询：取出这些符合条件的key对应的所有原始行。

方法二：自连接 #

通过自连接匹配同一key下不同状态的行，间接筛选出目标数据：

SELECT DISTINCT t1.*
FROM your_table t1
JOIN your_table t2
    ON t1.`key` = t2.`key`
    AND t1.status != t2.status
    AND t1.status IN ('a', 'b')
    AND t2.status IN ('a', 'b')
ORDER BY t1.`key`, t1.status;

• 自连接条件确保同一key下存在不同的status值，且两者都是'a'或'b'。
• 用DISTINCT避免重复返回同一行（因为每对a/b行会互相匹配一次）。

方法三：窗口函数（MySQL 8.0+） #

如果你的MySQL版本支持窗口函数，可以用更简洁的方式实现：

SELECT id, `key`, status
FROM (
    SELECT 
        id, `key`, status,
        -- 按key分组，计算该key下不同status的数量
        COUNT(DISTINCT status) OVER (PARTITION BY `key`) AS status_count
    FROM your_table
    WHERE status IN ('a', 'b')
) AS sub
WHERE status_count = 2
ORDER BY `key`, status;

• 窗口函数COUNT(DISTINCT status) OVER (PARTITION BY key)会为每一行计算其所属key对应的不同status总数。
• 外层筛选出总数为2的行，即为目标数据。

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