mysql - SQL 选择其中 id = 'x' 的所有记录的状态为 'y'

Question

我有一个表incoming，其中包含电子邮件列表及其附件。

我导入每个附件并处理它们，此时该行被标记为 5

当单个电子邮件的所有行都标记为 5 时，我可以移动/删除电子邮件，因为它已被正确处理。

但是，我正在努力编写一个 SQL 查询来选择 msg_uid，其中具有相同 msg_uid 的所有行的状态为 5

示例表

 -----------------------------------------------------
 | id | STATUS | i2d_id | msg_uid | i2d_msg          |
 -----------------------------------------------------
 | 16 | 5      | 98390  | 53      | Result is ready. |
 -----------------------------------------------------
 | 17 | 3      | 98391  | 53      | Result is ready. |
 -----------------------------------------------------
 | 18 | 5      | 98392  | 53      | Result is ready. |
 -----------------------------------------------------
 | 19 | 3      | 98393  | 53      | Result is ready. |
 -----------------------------------------------------
 | 20 | 5      | 98394  | 53      | Result is ready. |
 -----------------------------------------------------

这不允许我删除电子邮件(有些状态为 3

但是

-----------------------------------------------------
| id | STATUS | i2d_id | msg_uid | i2d_msg          |
-----------------------------------------------------
| 16 | 5      | 98390  | 53      | Result is ready. |
-----------------------------------------------------
| 17 | 5      | 98391  | 53      | Result is ready. |
-----------------------------------------------------
| 18 | 5      | 98392  | 53      | Result is ready. |
-----------------------------------------------------
| 19 | 5      | 98393  | 53      | Result is ready. |
-----------------------------------------------------
| 20 | 5      | 98394  | 53      | Result is ready. |
-----------------------------------------------------

会成功的。

Answer 1

 SELECT msg_uid 
 FROM yourTable
 GROUP BY msg_uid 
 HAVING COUNT(*) = COUNT(CASE WHEN STATUS = 5 THEN 1 END)