话不多说, 先看数据表信息.
数据表信息:
假设 A B C 三位小朋友期末考试成绩如下所示:
+-----+-----------+------|---------|
| name| chinese | math | english |
+-----+-----------+------|---------|
| A | 99 | 98 | 97 |
| B | 92 | 91 | 90 |
| C | 88 | 87 | 86 |
+-----+-----------+------|---------|
话不多说, 再看需求~
需求:
请使用 SQL 代码将以上成绩转换为如下格式:
+-----+-----------+------|
| name| subject |score |
+-----+-----------+------|
| A | chinese | 99 |
| A | math | 98 |
| A | english | 97 |
| B | chinese | 92 |
| B | math | 91 |
| B | english | 90 |
| C | chinese | 88 |
| C | math | 87 |
| C | english | 86 |
+-----+-----------+------|
话不多说, 进行拆解~
拆解:
select
name,
'chinese' as subject,
chinese as score
from
stu_score_2
union all
select
name,
'math' as subject,
math as score
from
stu_score_2
union all
select
name,
'english' as subject,
english as score
from
stu_score_2
order by
name, subject;
效果如下:
最后给大家介绍一下我这边的创建数据表和插入数据的操作步骤, 想要自己测试的话, 可以参考:
CREATE TABLE stu_score_2 (
name VARCHAR(10),
chinese INT,
math INT,
english INT
);
INSERT INTO stu_score_2 (name, chinese, math, english)
VALUES ('A', 99, 98, 97),
('B', 92, 91, 90),
('C', 88, 87, 86);
效果如下: