mysql - 员工培训分析查询的条件查询

我需要运行培训查询，返回以下问题的结果：“谁完成了此培训，但没有完成此培训？”
在下面的简化表中，我想知道哪个员工已经完成了培训ID 1（如“完成日期”字段中的日期所示），但还没有完成培训ID 7。

+-------------+-------------+----------------+
| emp_id      | training_id | completed_date |
+-------------+-------------+----------------+
| 1           | 1           | 2010-04-02     |
+-------------+-------------+----------------+
| 1           | 7           | Null           |
+-------------+-------------+----------------+
| 2           | 1           | Null           |
+-------------+-------------+----------------+
| 2           | 7           | Null           |
+-------------+-------------+----------------+

期望的结果是emp_id 1，我们希望根据查询参数返回他/她已完成的培训和未完成的培训：

+-------------+-------------+----------------+
| emp_id      | training_id | completed_date |
+-------------+-------------+----------------+
| 1           | 1           | 2010-04-02     |
+-------------+-------------+----------------+
| 1           | 7           | Null           |
+-------------+-------------+----------------+

我不知道如何使用常规查询来实现这一点，因为它似乎需要if逻辑。示例：返回一次培训完成的行，返回第二次培训未完成但仅在第一次培训完成时返回的行。
如何在sql中表达类似的内容？

最佳答案

可以使用exists子句

SELECT t.*
FROM training t

# which employee has completed training_id 1
WHERE t.training_id = 1 and t.completed_date is not null

#but has not finished training_id 7.
AND NOT EXISTS (
     SELECT * FROM training t2
     where t2.emp_id=t.emp_id
       and t2.training_id = 7
       and t2.completed_date is not null)

如果您想测试更复杂的内容，例如completed (4,5,6) but not (1,9)，则可以使用计数：

SELECT t.emp_id
FROM training t
WHERE t.training_id in (4,5,6) and t.completed_date is not null
group by t.emp_id
having count(distinct emp_id) = 3

AND NOT EXISTS (
     SELECT * FROM training t2
     where t2.emp_id=t.emp_id
       and t2.training_id in (1,9)
       and t2.completed_date is not null)

最后如果你需要完整的员工培训记录

SELECT e.*
FROM
(
    SELECT t.emp_id
    FROM training t
    WHERE t.training_id in (4,5,6) and t.completed_date is not null
    group by t.emp_id
    having count(distinct emp_id) = 3

    AND NOT EXISTS (
         SELECT * FROM training t2
         where t2.emp_id=t.emp_id
           and t2.training_id in (1,9)
           and t2.completed_date is not null)
) search
inner join training e on e.emp_id = search.emp_id
order by e.emp_id

关于mysql - 员工培训分析查询的条件查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5165552/