如何筛选commands表中含至少1条status=1记录的group_id对应全量数据？

现有表commands，表结构如下：

需求：对所有group_id相同的记录，若该分组中至少存在一条status为1的记录，则保留该分组全部记录；若该分组无status为1的记录，则移除该分组所有记录。

尝试用GROUP BY结合HAVING语句实现，但无法获取全量表数据或执行失败，初步思路如下：

SELECT COUNT(*) FROM commands GROUP BY group_id HAVING *condition*

示例数据 #

期望结果 #

方案1：子查询筛选有效分组ID #

先提取所有包含status=1的group_id，再关联原表获取这些分组的全部记录：

SELECT c.*
FROM commands c
WHERE c.group_id IN (
    SELECT DISTINCT group_id
    FROM commands
    WHERE status = 1
);

逻辑直观，兼容所有主流SQL数据库，适合快速实现需求。

方案2：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数计算每个分组内是否存在有效记录，一次扫描完成筛选，性能更优：

SELECT id, name, status, group_id
FROM (
    SELECT *,
           MAX(status) OVER (PARTITION BY group_id) AS has_valid
    FROM commands
) t
WHERE has_valid = 1;

MAX(status)在分组内只要有一个1，结果即为1，外层直接筛选出符合条件的行。

方案3：EXISTS关联查询 #

对每一行记录检查其所属分组是否存在status=1的记录：

SELECT c1.*
FROM commands c1
WHERE EXISTS (
    SELECT 1
    FROM commands c2
    WHERE c2.group_id = c1.group_id
      AND c2.status = 1
);

无需提前提取分组ID，部分数据库会对这类关联查询做优化，适合数据量较小的场景。

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