从status='yes'的表中选择distinct（num）、id、status

我只想用 group by 俱乐部：

select min(id) as id, num, 'YES' as status
from table t
group by num
having min(status) = max(status) and min(status) = 'YES';

SELECT * FROM table
WHERE status='YES' AND num NOT IN (SELECT num FROM table WHERE status='NO')

还是这个

SELECT * FROM table t
WHERE t.status='YES' AND NOT EXISTS (SELECT * FROM table WHERE status='NO' AND num=t.num)

只是一些其他的方法(（看起来是最干净的）
将数据分为两组，并确保“a”组在“b”组第一种方法中没有数据，在“a”组中使用仅限外部连接的返回记录，在“b”组中找不到相关的num。

SELECT Min(A.ID) as ID, A.num, A.status
FROM table A
LEFT JOIN table B
  on A.Num = B.Num
 and B.Status = 'NO'
WHERE A.Status = 'YES'
  and B.Num is null
GROUP BY A.Num, A.Status

第二种方法不存在。类似的只有“a”集中的num记录，而不是“b”集中的num记录。

SELECT min(A.ID) as ID, A.Num, A.Status
FROM table A
WHERE NOT EXISTS (SELECT 1 
                  FROM Table B
                  WHERE A.Num = B.Num
                    and B.Status = 'NO')
GROUP BY A.Num, A.Status

你可以用 HAVING :

SELECT MIN(id) AS id, num,'yes' AS status
FROM tab
GROUP BY num
HAVING SUM(status='NO') = 0;

dbfiddle演示