如何查询MySQL表中同时存在Active和Retired状态的火箭数据

方法一：子查询筛选目标火箭后关联原表 #

兼容所有MySQL版本的通用解法：

SELECT t.*
FROM space.test_1 t
WHERE t.Rocket IN (
    SELECT Rocket
    FROM space.test_1
    WHERE Status IN ('Active', 'Retired')
    GROUP BY Rocket
    HAVING COUNT(DISTINCT Status) = 2
);

逻辑说明：

1. 子查询先过滤出状态为Active或Retired的记录，按火箭分组后，通过COUNT(DISTINCT Status) = 2确保该火箭同时拥有两种状态。
2. 外层查询保留这些符合条件的火箭的所有原始行。

方法二：窗口函数（MySQL 8.0及以上版本适用） #

写法更简洁，无需嵌套子查询：

WITH rocket_status_stats AS (
    SELECT 
        *,
        COUNT(DISTINCT Status) OVER (PARTITION BY Rocket) AS status_type_count
    FROM space.test_1
    WHERE Status IN ('Active', 'Retired')
)
SELECT Rocket, Status
FROM rocket_status_stats
WHERE status_type_count = 2;

逻辑说明：

• 用PARTITION BY Rocket按火箭分组，统计每组内不同状态的数量。
• 筛选出状态数量为2的行，即为同时拥有两种状态的火箭的所有记录。

方法三：正确的自连接写法 #

通过自连接匹配同一火箭的不同状态记录：

SELECT DISTINCT t1.*
FROM space.test_1 t1
JOIN space.test_1 t2 
    ON t1.Rocket = t2.Rocket
    AND t1.Status != t2.Status
WHERE t1.Status IN ('Active', 'Retired')
  AND t2.Status IN ('Active', 'Retired');

逻辑说明：

• 自连接条件关联同一火箭且状态不同的记录。
• 限定两边状态都是目标状态，最后用DISTINCT避免重复行。

你之前的查询问题分析 #

1. 第一个查询：

select distinct(concat(rocket,'_', rocketstatus)) as BB
from space.test_1
group by bb
该查询仅生成火箭+状态的唯一组合，没有筛选出同时拥有两种状态的火箭，无法得到目标结果。

2. 第二个查询：

select a.rocket, a.rocketstatus from space.test_1 b
join space.test_1 a on a.id = b.id
where a.rocketstatus not in (select b.rocketstatus from space.test_1 b)

• 自连接条件a.id = b.id是连接同一条记录，无实际意义。
• a.rocketstatus not in (select b.rocketstatus from space.test_1 b)会返回空结果，因为子查询包含了所有存在的状态，a.rocketstatus必然属于其中。

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