[SQL智慧航行者] - 列转行1

话不多说, 先看数据表信息.

数据表信息:

假设 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);

效果如下: