mysql - 尝试将年份与大学生联系起来

Question

我有以下 View ，显示学生姓名、学生拥有的学分，我刚刚添加了一个名为“年份”的列，全部设置为 0。

+------------------+---------+------+
| StudentName      | Credits | Year |
+------------------+---------+------+
| Jon Macleod      |       0 |    0 |
| Carrie Gregory   |       0 |    0 |
| Matt Hayden      |       0 |    0 |
| Emily Scarlett   |      10 |    0 |
| Hailey Taylor    |      10 |    0 |
| Tj Davidson      |      10 |    0 |
| Alex Harry       |      20 |    0 |
| Matt Tosh        |      20 |    0 |
| Mitchell Gallant |      20 |    0 |
| Jon Harris       |      30 |    0 |
| Casey Macky      |      30 |    0 |
| James Doolittle  |      30 |    0 |
+------------------+---------+------+

我想让学生在获得 0 学分时就成为一年级学生。当学生获得 10 个学分时，他们是二年级学生，依此类推。

我有以下脚本，但无法弄清楚为什么它不起作用。

CREATE OR REPLACE VIEW Year AS
SELECT S.stu_name AS StudentName, SUM(Credits) AS Credits, 0 AS Year
        FROM Student AS S JOIN Enrollment AS E
        ON S.stu_id = E.stu_id
        Case 
            when (Year = '0' and Credits = 0 then 'FirstYear'
            when (Year = '0' and Credits = 10 then 'SecondYear' 
            else Year
         end as Year
        GROUP BY S.stu_id
        ORDER BY Credits;

Answer 1

试试这个:

因为我错过了 MySql 不允许子查询的事实，所以这里是一个正确的解决方案:

CREATE OR REPLACE VIEW Year AS
   SELECT S.stu_name AS StudentName, SUM(Credits) AS Credits, 
          Case When SUM(Credits)=0 then 'First Year'
               When SUM(Credits)=10 then 'Second Year'
               When SUM(Credits)=20 then 'Third Year'
               When SUM(Credits)=30 then 'Fourth Year'
               When SUM(Credits)=40 then 'Fifth Year'
               else '' end as year
     FROM Student AS S INNER JOIN Enrollment AS E
       ON S.stu_id = E.stu_id
     GROUP BY S.stu_id
    order by E.credits;

在 fiddle 上查看:http://sqlfiddle.com/#!2/776c3/1