MySQL 显示所需类(class)已完成

Question

//更新:提供的答案有效，但我遇到了另一种情况。如果学生转到另一个职位，当有重叠时，他们需要保留完成前一个职位的学分。情况见下:

我有 2 门类(class)，类(class) A 和类(class) B，这两个培训代码中的每一个都是必需的。如果学生在培训代码 10 下学习类(class) A 和类(class) B，然后转到新职位(培训代码 30)，他们将显示异常，因为他们的代码已更改而未完成类(class) A 和类(class) B。虽然类(class)是一样的。我已经尝试比较描述的数量，但它不起作用。类(class)名称是培训表上的 activityId 以及上面的 require 培训表。有什么想法吗？谢谢

我正在尝试编写一个 MySql 脚本来确定学生何时完成或未完成所​​需培训列表(类(class))中的所有类(class)。在下面的示例中，我需要证明 John Doe 已完成 0 级培训的 1、2 和 3 类。我还需要证明 Jane 没有由于缺少第 2 类，因此完成了所有类(class)。我试图为类分配一个订单号，所以如果你到达 ordernum 3 (Class3) 你是完成，但如果学生没有参加第 2 课，我会遗漏一些空白。此外，这需要贯穿 1500 名学生以显示谁已经/没有完成0级训练。

tbltraining

firstname
lastname
email
organization
activity
description
status
jobcode

Ex tbl训练

John    |   Doe     |   jdoe@email.com  |   1233    |   Class1      |   Certification   |   Completed   |   030 
John    |   Doe     |   jdoe@email.com  |   1233    |   Class2      |   Certification   |   Completed   |   030 
John    |   Doe     |   jdoe@email.com  |   1233    |   Class3      |   Certification   |   Completed   |   030 
John    |   Doe     |   jdoe@email.com  |   1233    |   OtherClass  |   Certification   |   Completed   |   030 
John    |   Doe     |   jdoe@email.com  |   1233    |   OtherClass2 |   Certification   |   Completed   |   030 
Jane    |   Doe     |   jadoe@email.com |   3311    |   Class1      |   Certification   |   Completed   |   010 
Jane    |   Doe     |   jadoe@email.com |   3311    |   Class3      |   Certification   |   Completed   |   010 
James   |   Doe     |   jmdoe@email.com |   1233    |   Class1      |   Certification   |   Completed   |   030 
James   |   Doe     |   jmdoe@email.com |   1233    |   Class2      |   Certification   |   Incomplete  |   030 

tblreqtraining

trainingname
trainingdescription
trainingcode
traininglevel

Ex tblreqtraining

Class1  |   Certification   |   30  |   0
Class2  |   Certification   |   30  |   0
Class3  |   Certification   |   30  |   0
Class1  |   Certification   |   10  |   0
Class2  |   Certification   |   10  |   0
Class3  |   Certification   |   10  |   0
Class4  |   Certification   |   10  |   0

• Activity需要匹配trainingname
• Description需要匹配trainingdescription
• jobcode需要匹配trainingcode

查询:

SELECT tra.firstname, tra.lastname, tra.email, tra.organization, tra.activity, tra.description, 
        tra.jobcode, req.trainingname, req.traininglevel, req.trainingcode
FROM tbltraining tra
RIGHT JOIN tblreqtraining req on tra.activity = req.trainingname and right(tra.jobcode, 2) = req.trainingcode
WHERE (right(tra.jobcode,2) = '30' or right(tra.jobcode,2) = '10')
and tra.description = 'Certification'
and tra.status = 'Completed'
and req.traininglevel = 0

对此有任何想法或方向，我们将不胜感激。

Answer 1

获取每个训练代码的类(class)数，以及每个训练代码每个学生完成的类(class)数。如果学生的计数与类(class)的计数不同，则他们没有完成所有类(class)。

SELECT tra.firstname, tra.lastname, tra.trainingcode,
        CASE IFNULL(tra.completed_count, 0)
            WHEN req.class_count THEN 'Completed All'
            ELSE 'Not completed'
        END
FROM (
    SELECT trainingcode, COUNT(*) class_count
    FROM tblreqtraining
    WHERE traininglevel = 0
    GROUP BY trainingcode
) AS req
LEFT JOIN (
    SELECT firstname, lastname, RIGHT(jobcode, 2) AS trainingcode, COUNT(*) AS completed_count
    FROM tbltraining AS tra
    JOIN tblreqtraining AS req ON RIGHT(tra.jobcode, 2) = req.trainingcode AND tra.activity = req.trainingname
    WHERE tra.description = 'Certification' AND tra.status = 'Completed'
    AND req.traininglevel = 0
    GROUP BY firstname, lastname, trainingcode
) AS tra ON tra.trainingcode = req.trainingcode