努力将年数与大学生联系起来

Question

我有下面的视图，它显示了学生的名字，学分，我刚刚添加了一个名为have的列，全部设置为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;

在小提琴上看到它：http://sqlfiddle.com/#!2/776c3/1

Answer 2

你可以试试这样的东西：

SELECT S.stu_name AS StudentName, SUM(Credits) AS Credits,
    Case Credits
        when 0 then 'FirstYear'
        when 10 then 'SecondYear' 
        else Year
     end 'year'
              FROM student AS S
    GROUP BY S.stu_id
    ORDER BY Credits;