MySQL行列转化方式_Mysql

初始化表结构

create table `student_scores` (
  `student_id` int not null,
  `student_name` varchar(50) default null,
  `math_score` int default null,
  `english_score` int default null,
  `science_score` int default null,
  primary key (`student_id`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

insert into student_scores (student_id, student_name, math_score, english_score, science_score) values
(1, 'alice', 85, 90, 78),
(2, 'bob', 76, 88, 92),
(3, 'charlie', 90, 85, 80);

查询表数据：

行转化为列

由于不是我们想要的格式，我们将其转化为列式结构：

-- 行数转化为列
select student_id, student_name, 'math' as subject, math_score as score from student_scores
union all
select student_id, student_name, 'english' as subject, english_score as score from student_scores
union all
select student_id, student_name, 'science' as subject, science_score as score from student_scores;

执行结果：

列转化为行

将其作为一张临时表，对其进行行列转化：

select student_id,student_name,
min(case when subject = 'math' then score end ) as math_score,
min(case when subject = 'english' then score end )as english_score,
min(case when subject = 'science' then score end )as science_score 
from  (
    select student_id, student_name, 'math' as subject, math_score as score from student_scores
    union all
    select student_id, student_name, 'english' as subject, english_score as score from student_scores
    union all
    select student_id, student_name, 'science' as subject, science_score as score from student_scores
) as unpivoted
group by unpivoted.student_id,unpivoted.student_name

执行结果：