mysql中sql行列转换

1.列转行

select class_id,MAX(CASE kemu when '语文' then score ELSE 0 end)as '语文' ,
MAX(CASE kemu when '数学' then score ELSE 0 end)as '数学' ,
MAX(CASE kemu when '英语' then score ELSE 0 end)as '英语'
FROM scoreinfo GROUP BY stuent_id

 

 2.依据查询的结果机创建一个新表

CREATE table pei_new (select class_id,MAX(CASE kemu when '语文' then score ELSE 0 end)as '语文' ,
MAX(CASE kemu when '数学' then score ELSE 0 end)as '数学' ,
MAX(CASE kemu when '英语' then score ELSE 0 end)as '英语'
FROM scoreinfo GROUP BY stuent_id)

 

 3.复制一张表 ,只复制表结构

CREATE table pei_new like scoreinfo;

4.行转列

 

 

 

SELECT class_id,'语文' as 科目,语文 as 成绩 FROM pei_new
UNION
SELECT class_id,'数学',数学 FROM pei_new
union
SELECT class_id,'英语',英语 FROM pei_new