行转列
case when then else end as 列的名称
select studentno,max(高等数学1) math1 ,max(高等数学2) math2,max(java编程) java,max(hadoop理论) hadoop from (select studentno,case when subjectno = 1 then studentresult else 0 end 高等数学1,
case when subjectno = 2 then studentresult else 0 end 高等数学2,
case when subjectno = 3 then studentresult else 0 end java编程,
case when subjectno = 4 then studentresult else 0 end hadoop理论
from result where studentno = 1000) t group by studentno;
举例
经典50题 每门科目的最高分展示在一行
select max(语文),max(数学),max(英语) from (select case when t.c_id = 1 then t.max else 0 end 语文,
case when t.c_id = 2 then t.max else 0 end 数学,
case when t.c_id = 3 then t.max else 0 end 英语
from (select sc.c_id,max(sc.s_score) max from score sc group by sc.c_id) t) t2;
列转行
将学号1的学生的成绩展示在一格内
select s_id , group_concat(s_score)from score where s_id=1;
还能将成绩从高到低展示在一起,并用#隔开
select s_id , group_concat(distinct s_score order by s_score desc separator '#')from score where s_id=1;
字符串拼接
select concat(s_id,c_id,s_score) from score limit 2;
用空格隔开
select concat_ws(' ',s_id,c_id,s_score) from score limit 2;