mysql中行转列

1.使用case when then else end

select 
case 
when stuid=1002 then "李四"  #如果stuid=1002则结果为李四
else "不是李四"   #否则输出这个结果
end as "是否名字为李四"  #end结束标志 必须要有   将其单独作为一个字段名称
from student;  
#总的意思是  在student表中如果stuid是1002的则返回"李四",否则返回值"不是李四"

结果为:

 

这是原来表的数据

select stuid ,stunm,
Max(CASE WHEN coursenm ="大学语文" THEN scores ELSE 0  END )  as "大学语文",
Max(CASE WHEN coursenm ="新视野英语" THEN scores ELSE 0  END ) as "新视野英语",
Max(CASE WHEN coursenm ="离散数学" THEN scores ELSE 0  END ) as "离散数学",
Max(CASE WHEN coursenm ="概率论与数理统计" THEN scores ELSE 0  END ) as "概率论与数理统计",
Max(CASE WHEN coursenm ="线性代数" THEN scores ELSE 0  END ) as "线性代数",
Max(CASE WHEN coursenm ="高等数学(一)" THEN scores ELSE 0  END ) as "高等数学(一)",
Max(CASE WHEN coursenm ="高等数学(二)" THEN scores ELSE 0  END ) as "高等数学(二)"
from rowtocol
GROUP BY stuid;

 

---------------------------------------------------2020.4.11分割线-----------------------------------------------------------------------------------------

列转行：
select name,
max(case when subject="语文"
then score end)as 语文,
max(case when subject="数学"
then score end)as 数学,
max(case when subject="英语"
then score end)as 英语
from test
group by name;

行转列：
select name,语文 as score , '语文成绩' subject from test1
union
select name,数学 as score , '数学成绩' subject from test1
UNION
select name,英语 as score , '英语成绩' subject from test1;

 

原来以为可以多水一篇文章呢