MySQL 查询帮助

Question

以下是我正在使用的表的描述：

describe mjla_db.StudentRecordTable2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classId   | varchar(20) | NO   | MUL | NULL    |       |
| studentId | varchar(20) | NO   | MUL | NULL    |       |
| quizGrade | tinyint(4)  | YES  |     | NULL    |       |
| quizId    | int(11)     | NO   | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

---

这是数据库中的示例数据：

+------------+-----------+------------+---------+------------+
| Student ID | Last Name | First Name | Quiz ID | Quiz Grade |
+------------+-----------+------------+---------+------------+
| A1         | Cat       | Tom        |      19 |       75   |
| A2         | pancake   | Harry      |      19 |       65   |
| A5         | Worthy    | Dick       |      19 |       NULL |
| A1         | Cat       | Tom        |      20 |       55   |
| A2         | pancake   | Harry      |      21 |       NULL |
| A2         | pancake   | Harry      |      20 |       47   |
| A5         | Worthy    | Dick       |      20 |       95   |
| A1         | Cat       | Tom        |      21 |       55   |
| A5         | Worthy    | Dick       |      21 |       95   |
+------------+-----------+------------+---------+------------+
3 rows in set (0.00 sec)

---

我试图获得的结果类似于以下内容：

+------------+-----------+------------+---------+------------+------------+
| Student ID | Last Name | First Name | Quiz 19 | Quiz 20    | Quiz 21    |
+------------+-----------+------------+---------+------------+------------+
| A1         | Cat       | Tom        |      75 |       55   |       55   |
| A2         | pancake   | Harry      |      65 |       47   |       NULL |
| A5         | Worthy    | Dick       |     NULL|       95   |       95   |
+------------+-----------+------------+---------+------------+------------+

• 其中学生 ID 列是唯一的。
• 测验列的继续位置取决于测验的数量 原来的表。测验栏包含每个的成绩 各自的学生。

Answer 1

试试这个：

 select s.StudentId, s.FirstName, s.LastName, 
 Case when s.QuizId = 19 then quizGrade end as 'Quiz 19',
 Case when s.QuizId = 20 then quizGrade end as 'Quiz 20',
 Case when s.QuizId = 21 then quizGrade end as 'Quiz 21'
 from StudentRecordTable2 sr 
 inner join Students s on sr.StudentId = s.StudentId

参见这个