MYSQL：仅当所有行都满足条件时才返回一行的SQL查询

Question

考虑三个表——

  users

    id      |   type
 -----------|------------
    1       |   a
    2       |   b   
    3       |   c

 types  

    id      |   type
 -----------|------------
    a       |   X
    a       |   Y   
    b       |   X
    c       |   X
    c       |   Y
    c       |   Z

 training_status

    id      |   training|   status
 -----------|-----------|-------------
    1       |   X       |   F
    2       |   X       |   S
    2       |   Y       |   S
    3       |   X       |   F
    3       |   Y       |   S

每个用户都有一个类型，而类型定义了每个特定类型的用户需要完成的培训。 training_status包含用户参加的所有培训的状态和结果（S，F）。如果用户还没有参加培训，则不会有该培训的行。

我想找出所有已成功完成其必须参加的所有培训的用户。

以下是我的思路：

select
  id
from users
  join types
    using (type)
  left join training_status
    using (id,type)
where status NOT IN(None, F);

显然，这不是正确的查询，因为即使用户已经完成了其中一项培训，我们仍然会得到该行。在上述示例中，我想获得id=2，因为他已经完成了其类型的两个培训。

- zonked.zonda

---

感谢Peter的回答：SELECT id, SUM(status IS NULL or Status is not 'S') as pending from users join types using (type) join training_status using (id, training) group by id having pending = 0; - zonked.zonda

Answer 1

尝试

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 WHERE s.status IS NOT NULL 
 GROUP BY u.id
HAVING COUNT(t.type) = SUM(CASE WHEN s.status = 'S' THEN 1 ELSE 0 END)

或者

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 GROUP BY u.id
HAVING MAX(s.status IS NULL OR s.status = 'F') = 0

输出:

+------+
| id   |
+------+
|    2 |
+------+

这里是SQLFiddle演示

注：该文本已经是中文，无需翻译。

- peterm

---

我想获取已完成所有培训的用户，而不是那些没有完成的用户。将这个查询转换为...WHERE s.status IS NOT NULL AND s.status = 'S' 是行不通的。 - zonked.zonda

@zonked.zonda 抱歉没有注意到，看更新后的答案。 - peterm

第二个查询看起来很有前途。谢谢Peter。让我试一下，然后我会更新答案。 - zonked.zonda

我稍微修改了一下你的查询语句，使其更易读。SELECT id, SUM(status IS NULL or Status is not 'S') as pending from users join types using (type) join training_status using (id, training) group by id having pending = 0; 谢谢 @peter - zonked.zonda

Answer 2

试试这个

SELECT *
FROM users
WHERE id NOT IN (
SELECT u.id
  FROM users u 
  JOIN types t 
    ON u.type = t.type 
  LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 WHERE s.status IS NULL OR s.status = 'F')

- Chamal

---

用户表非常大。获取整个表，然后删除几行将非常昂贵。 - zonked.zonda

Answer 3

试试这个

select distinct
  u.id
from users u
  left join types t
    on u.type = t.type
  left join training_status ts
    on ts.training = t.training
where ts.status is not null
    and ts.status != 'F'

- Nitu Bansal

---

这与我提出的查询（请参见问题）相同，但它返回用户3。 - zonked.zonda

Answer 4

这应该可以工作：

SELECT id 
FROM users
WHERE id not in (SELECT x.id FROM (SELECT u.id AS id, t.type AS type, 'S' AS status
                FROM users u, types t
                WHERE u.type = t.id
                EXCEPT
                SELECT id, training, status
                FROM training_status
                                  ) AS x (id, type, status)
        )

请注意，它使用了集合差运算符EXCEPT，该运算符给出所有可能的用户和他们成功培训组合以及当前培训状态之间的行差异。如果所有用户的当前状态都已完成，则差异应该不会产生任何行。非零结果意味着存在未完成所需培训的用户。最外层的选择提供了未在未完成培训用户列表中的用户列表，即已完成培训的用户列表！

在MS SQL服务器上执行您的数据时，它将给出答案2，这是唯一成功完成培训的人。

以下是MySQL版本：

SELECT id 
FROM users
WHERE id not in (SELECT x.id 
             FROM (SELECT u.id AS id, t.type AS type, 'S' AS status
                   FROM users u, types t
                   WHERE u.type = t.id 
                   ) x  
             WHERE NOT EXISTS
                    (SELECT ts.id, ts.training, ts.status
                     FROM training_status ts
                     WHERE ts.id = x.id AND 
                           ts.training = x.type AND
                           ts.status = x.status
                     )
            )

+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

- mvsagar