如何筛选所有状态均为NOK的服务（排除含OK状态的服务）

问题背景 #

现有一张包含服务名称（Service）和服务状态（state）的表，同一服务会部署在多台主机上，状态可能存在差异。需要筛选出所有行状态都是NOK的服务——只要服务存在任意一行OK状态，就排除该服务。

示例源数据 #

期望结果 #

仅返回ServiceB，因为它的所有行状态均为NOK，而ServiceA存在OK状态的行。

现有尝试的问题 #

你之前用分组统计的SQL：

WITH a AS (
    SELECT service, state, count(*)
    FROM table
    GROUP BY service, state
)
SELECT * FROM a;

得到的是按service+state组合的计数，但这种分组方式无法直接判断某个服务是否存在非NOK状态。如果用WHERE state != 'NOK'，只会过滤掉单条OK行，但ServiceA的NOK行依然会被保留，导致结果错误。

解决方案 #

以下是几种高效的实现方式：

方法1：GROUP BY + HAVING 过滤（推荐，适用于状态只有OK/NOK的场景） #

通过分组后检查服务的状态范围，确保所有状态都是NOK：

SELECT service
FROM your_table
GROUP BY service
HAVING MIN(state) = 'NOK' AND MAX(state) = 'NOK';

如果状态不止OK/NOK两种，用更通用的统计方式：

SELECT service
FROM your_table
GROUP BY service
HAVING SUM(CASE WHEN state != 'NOK' THEN 1 ELSE 0 END) = 0;

原理：统计每个服务下非NOK状态的行数，只有当这个计数为0时，说明该服务所有行都是NOK。

方法2：NOT EXISTS 子查询 #

直接排除存在非NOK状态的服务：

SELECT DISTINCT service
FROM your_table t_main
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t_sub
    WHERE t_sub.service = t_main.service
      AND t_sub.state != 'NOK'
);

原理：找到所有不存在非NOK状态行的服务，DISTINCT用来去除同一服务的重复行。

方法3：窗口函数标记 #

用窗口函数给每个服务计算非NOK行数，再筛选：

WITH service_status AS (
    SELECT 
        service,
        SUM(CASE WHEN state != 'NOK' THEN 1 ELSE 0 END) OVER (PARTITION BY service) AS non_nok_total
    FROM your_table
)
SELECT DISTINCT service
FROM service_status
WHERE non_nok_total = 0;

原理：窗口函数按服务分组统计非NOK的总行数，筛选出计数为0的服务即可。

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