mysql - 在 DISTINCT 条件中选择 DISTINCT 列

Question

当它的状态仅为"is"时，我想从“num”中选择不同的值，而不是立即包括“否”？

表:

+--------+-----+--------+
| id     | num | status |
+--------+-----+--------+
| 364519 | AAA | YES    |
| 364522 | BBB | YES    |
| 364525 | AAA | NO     |
| 364527 | CCC | YES    |
| 364530 | DDD | YES    |
| 364532 | EEE | YES    |
| 364535 | FFF | YES    |
| 364537 | DDD | YES    |
| 364538 | DDD | NO     |
| 364539 | FFF | NO     |
+--------+-----+--------+

需要的结果:

+--------+-----+--------+
| id     | num | status |
+--------+-----+--------+
| 364522 | BBB | YES    |
| 364527 | CCC | YES    |
| 364532 | EEE | YES    |
+--------+-----+--------+

Answer 1

你可以使用HAVING:

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

DBFiddle Demo