MYSQL：SQL查询仅在所有行满足条件时返回行

Consider three tables -

考虑三个表 -

  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

Each user has a type, and types defines the trainings that each user of a particular type have to complete.

每个用户都有一个类型，类型定义了特定类型的每个用户必须完成的培训。

training_status contains status of all the trainings that a user has taken and its result (S,F). It a user is yet to take a training, there won't be any row for that training.

training_status包含用户已完成的所有培训及其结果（S，F）的状态。用户尚未参加培训，不会有任何培训。

I would like to find out all users that have successfully completed all the trainings that they have to take.

我想找出所有已成功完成所有培训的用户。

Here's the direction that I am thinking in:

这是我在思考的方向：

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

Obviously this is not the right query because even if the user has completed one of the trainings, we get that row. In the aforementioned example, I'd like to get id = 2 because he has completed both trainings of its type.

显然这不是正确的查询，因为即使用户已完成其中一项培训，我们也会获得该行。在前面提到的例子中，我想得到id = 2，因为他已经完成了两种类型的训练。

4 个解决方案

#1

Try

尝试

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)

or

要么

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

Output:

输出：

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

Here is SQLFiddle demo

这是SQLFiddle演示

#2

Try this

尝试这个

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')

#3

Try this

尝试这个

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'

#4

This should work:

这应该工作：

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)
        )

Note that it uses set difference operator EXCEPT which gives difference of rows of all possible users and their successful training combinations and current training status. If the current status is complete for all users, the difference should yield no rows. Non zero result means there are users who did not complete required training. The outermost select gives list of users who are not in the list of users who did not complete the training, i.e, list of users who completed!

请注意，它使用设置差异运算符EXCEPT，它给出了所有可能用户的行数及其成功的训练组合和当前训练状态的差异。如果所有用户的当前状态都已完成，则差异不应产生任何行。非零结果意味着有些用户没有完成所需的培训。最外面的选择给出了不在未完成培训的用户列表中的用户列表，即完成的用户列表！

When executed in MS SQL server for your data it gives answer 2 who is the only one who completed training successfully.

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

Here is MySQL Version:

这是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)