mysql case when

有两张表，一张学生分数表，一张等级表，根据分数高低得出成绩等级，模拟了下，现大概记录下整个过程。

 

<<A href="mailto:root@localhost">root@localhost nigel 13:51:09>select * from students;
+----+--------+-------+-------+
| id | name   | sex   | score |
+----+--------+-------+-------+
|  1 | leo    | man   |    65 |
|  2 | jacky  | man   |    78 |
|  3 | lily   | woman |    82 |
|  4 | leo2   | man   |    96 |
|  5 | jacky2 | man   |    46 |
|  6 | lily2  | woman |    80 |
+----+--------+-------+-------+
6 rows in set (0.00 sec)

<<A href="mailto:root@localhost">root@localhost nigel 13:51:15>select * from ref;
+--------+-----------+
| score  | grade     |
+--------+-----------+
| <60    | fail      |
| 60-69  | pass      |
| 70-79  | good      |
| 80-89  | very good |
| 90-100 | excellent |
+--------+-----------+
5 rows in set (0.00 sec)

<<A href="mailto:root@localhost">root@localhost nigel 14:01:47>select id, name,score ,case 
  when score < 60 then 'fail'
  when score < 70 then 'pass'
  when score < 80 then 'good'
  when score < 90 then 'very good'
  else "excellent "
  end as grade
from students;
+----+--------+-------+------------+
| id | name   | score | grade      |
+----+--------+-------+------------+
|  1 | leo    |    65 | pass       |
|  2 | jacky  |    78 | good       |
|  3 | lily   |    82 | very good  |
|  4 | leo2   |    96 | excellent  |
|  5 | jacky2 |    46 | fail       |
|  6 | lily2  |    80 | very good  |
+----+--------+-------+------------+
6 rows in set (0.00 sec)

为了好看，我们也可以根据分数进行排序。