MySql 查询 - 显示状态

Question

我有学生表和学生类(class)表。我正在计算学生完成认证类(class)的百分比。我没有要求显示百分比，而是显示如下状态:

Percent Complete = 0; Training Status would be Assigned; Certification
Status-N/A Percent Complete = 33;Training Status would be In-Progress;
Certification Status-Pending Percent Complete = 66;Training Status
would be Complete; Certification Status-In-Progress

如何计算百分比并显示上述措辞而不是百分比？

SELECT CONCAT(Student.LastName,', ', Student.FirstName) AS "FULL NAME",        
    studentcourse.CourseID as "COURSE", 
    studentcourse.Status AS "TRAINING STATUS", studentcourse.PercentComplete,
    studentcourse.status as 'CERTIFICATION STATUS' 
FROM studentcourse, student, Course
where Student.OrgID='DECC'
    AND student.studentID=studentcourse.StudentID
    AND Student.IsActiveFlag='1'
    AND Course.CourseID=StudentCourse.CourseID

示例数据:百分比列将被删除。

FULL NAME         COURSE                 TRAINING STATUS  %Complete CERT STATUS
Adkins, Willa         DECC_PER              not attempted   0   not attempted
Adkins, Willa         LMS_Training_DECC     not attempted   0   not attempted
Akers, Dianne         DECC_PER              not attempted   0   not attempted
Akers, Dianne         LMS_Training_DECC     not attempted   0   not attempted
Alexander, Richard    DECC_PER              not attempted   0   not attempted
Alexander, Richard    LMS_Training_DECC     not attempted   0   not attempted
Altamirando, Ardella  8570_Pending_CE_NE        completed   100 completed
Altamirando, Ardella  8570_Pending_IA           completed   100 completed

尝试使用 CASE 但出现语法错误。

Answer 1

此查询假设百分比级别如下:

• 0%:培训状态已分配；证书状态 N/A
• >0% 至 33%:训练状态进行中；证书状态待处理
• >33% 至 66%:培训状态完成；证书状态进行中
• >66%:???

如果此假设错误，您需要调整下面的 CASE 语句中的数字。

SELECT
  CONCAT(Student.LastName,', ', Student.FirstName) AS "FULL NAME",        
  studentcourse.CourseID as "COURSE", 
  studentcourse.PercentComplete,
  CASE
    WHEN studentcourse.PercentComplete = 0 THEN 'Assigned'
    WHEN studentcourse.PercentComplete <= 33 THEN 'In-Progress'
    WHEN studentcourse.PercentComplete <= 66 THEN 'Complete'
    ELSE 'whatever comes next'
    END AS "TRAINING STATUS",
  CASE
    WHEN studentcourse.PercentComplete = 0 THEN 'N/A'
    WHEN studentcourse.PercentComplete <= 33 THEN 'Pending'
    WHEN studentcourse.PercentComplete <= 66 THEN 'In-Progress'
    ELSE 'whatever comes next'
    END AS "CERTIFICATION STATUS"
FROM studentcourse, student, Course
WHERE Student.OrgID='DECC'
  AND student.studentID=studentcourse.StudentID
  AND Student.IsActiveFlag='1'
  AND Course.CourseID=StudentCourse.CourseID

最后一点:新的 ANSI 连接可以通过将 WHERE 逻辑与 JOIN 逻辑分开来使查询更易于编写和读取:

SELECT
  . . . same as above
FROM studentcourse
INNER JOIN student ON studentcourse.StudentID = student.studentID
INNER JOIN Course ON StudentCourse.CourseID = Course.CourseID
WHERE Student.OrgID='DECC'
  AND Student.IsActiveFlag='1'