mysql - 使用 Case 语句创建存储过程

Question

我正在尝试创建一个存储过程，但我似乎弄错了。我只在需要时使用 SQL，所以请原谅开发人员并提供帮助。我想在存储过程中包含一个 SQL 查询，该查询在两个表之间进行内部联接:user_students 和 Student_grades_summary。这是我写的存储过程:

CREATE PROCEDURE GradeStudents( in scoreover100 float, out s_grade char(3))
BEGIN 
CASE
        WHEN (student_grades_summary.aggregate >= 80 && student_grades_summary.aggregate <= 100 && student_grades_summary.level = 'JSS') THEN
            SET student_grades_summary.grade = 'A1';
        WHEN(student_grades_summary.aggregate >= 70 AND student_grades_summary.aggregate <= 79 AND student_grades_summary.level = 'JSS') THEN
            SET student_grades_summary.grade = 'B1';
        WHEN (student_grades_summary.aggregate >= 60 AND student_grades_summary.aggregate <= 69 AND student_grades_summary.level = 'JSS') THEN
            SET student_grades_summary.grade = 'B2';
        WHEN(student_grades_summary.aggregate >= 55 AND student_grades_summary.aggregate <= 59 AND student_grades_summary.level = 'JSS') THEN
            SET student_grades_summary.grade = 'P1';
        WHEN(student_grades_summary.aggregate >= 50 AND student_grades_summary.aggregate <= 54 AND student_grades_summary.level = 'JSS') THEN
            SET student_grades_summary.grade = 'P2';
        WHEN(student_grades_summary.aggregate <= 49 AND student_grades_summary.level = 'JSS')
            SET student_grades_summary.grade = 'F';
        WHEN(student_grades_summary.aggregate >= 85 AND student_grades_summary.aggregate <= 100 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'A1';
        WHEN(student_grades_summary.aggregate >= 80 AND student_grades_summary.aggregate <= 84 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'B2';
        WHEN(student_grades_summary.aggregate >= 75 AND student_grades_summary.aggregate <= 79 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'B3';
        WHEN(student_grades_summary.aggregate >= 70 AND student_grades_summary.aggregate <= 74 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'C4';
        WHEN(student_grades_summary.aggregate >= 65 AND student_grades_summary.aggregate <= 69 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'C5';
        WHEN(student_grades_summary.aggregate >= 60 AND student_grades_summary.aggregate <= 64 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'C6';
        WHEN(student_grades_summary.aggregate >= 55 AND student_grades_summary.aggregate <= 59 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'D7';
        WHEN(student_grades_summary.aggregate >= 50 AND student_grades_summary.aggregate <= 54 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'E8';
        WHEN(student_grades_summary.aggregate <= 49 AND student_grades_summary.level = 'SSS') THEN
            SET student_grades_summary.grade = 'F9';
END CASE;

这是我尝试将存储过程包含在其中的 SQL 查询。基本上，我尝试使用存储过程将值添加到 Student_grades_summary 中的成绩列。不管怎样，这是sql:

select user_students.fname as FirstName, user_students.lname as LastName
     , student_grades_summary.subject as CoreSubjects
     , student_grades_summary.aggregate as Scoreover100
     , student_grades_summary.subjectrank as Position
     , student_grades_summary.term as Term
     , student_grades_summary.level as Level
     , student_grades_summary.class as Class
     , student_grades_summary.section as Section
     , student_grades_summary.session as Session
FROM student_grades_summary 
JOIN user_students 
   ON student_grades_summary.level = user_students.level 
   AND student_grades_summary.class = user_students.class 
   AND student_grades_summary.section = user_students.section 
JOIN config_grades 
   ON student_grades_summary.level = config_grades.level 
WHERE user_students.level = 'JSS' 
   AND user_students.class = '2' 
   AND student_grades_summary.session = '2015/2016'

所以在 phpmyadmin 中，我收到很多红色波浪线，表示 SQL 中有错误。我尝试查看代码并找出错误所在，但未能成功。

Answer 1

我一次又一次地看到这一点。你没有用 SQL 来思考。在 SQL 中你需要用集合来思考。不要在子程序中思考，而要在集合中思考。在本例中，您的集合是成绩标准列表，我们可以将其表示为表格(我将其称为grade_lookup)

aggmin aggmax lvl   grade
80       100  'JSS' 'A1'      
70       79   'JSS' 'B1'
60       69   'JSS' 'B2'
55       59   'JSS' 'P1'
50       54   'JSS' 'P2'
0        49   'JSS' 'F' 
85       100  'SSS' 'A1' 
80       84   'SSS' 'B2'
75       79   'SSS' 'B3'
70       74   'SSS' 'C4'
65       69   'SSS' 'C5'
60       64   'SSS' 'C6'
55       59   'SSS' 'D7'
50       54   'SSS' 'E8'
0        49   'SSS' 'F9'

一旦您拥有了grade_lookup表，您就可以加入它并获得您的成绩——无需调用函数——如下所示:

select user_students.fname as FirstName, user_students.lname as LastName
     , s.subject as CoreSubjects
     , s.aggregate as Scoreover100
     , s.subjectrank as Position
     , s.term as Term
     , s.level as Level
     , s.class as Class
     , s.section as Section
     , s.session as Session

     -- the result of our lookup
     , COALESCE(L.grade ,'lookup fail') as Grade

FROM student_grades_summary S
JOIN user_students 
   ON student_grades_summary.level = user_students.level 
   AND student_grades_summary.class = user_students.class 
   AND student_grades_summary.section = user_students.section 
JOIN config_grades 
   ON student_grades_summary.level = config_grades.level 

-- join to grade lookup
LEFT JOIN GRADE_LOOKUP L ON s.aggregate >= L.aggmin 
                   and s.aggregate <= L.aggmax 
                   and s.level = L.lvl

WHERE user_students.level = 'JSS' 
   AND user_students.class = '2' 
   AND student_grades_summary.session = '2015/2016'

note, I just stuck this into your select statement -- I've no idea if your select statement is correct or not -- don't consider my re-using your example code as validation of it's correctness.