面试问题【mysql行转列，列转行】

1、行转列

max/sum加上group by【注：在工作当中很少用到存储过程，更多的是用java等语言配合mysql解决实际问题，所以就不说存储过程的方式】

代码：

-- 行转列：主要是通过max/sum实现的，如果要动态可以借助java，写个for循环。循环传值【列：以时间位列，显示近15天的出库量】

-- 方法一

SELECT s_name,max(case when c_name='java' then score else 0 end) java,max(case when c_name='c#' then score  else 0 end) cc,

max(case when c_name='php' then score  else 0 end)  php

FROM user_score_info GROUP BY s_name;

SELECT s_name,sum(case when c_name='java' then score else 0 end) java,sum(case when c_name='c#' then score  else 0 end) cc,

sum(case when c_name='php' then score  else 0 end)  php

FROM user_score_info GROUP BY s_name;

-- 方法二

select distinct a.s_name ,IFNULL((select score from user_score_info b where b.s_name = a.s_name and c_name="java"),0) java,

IFNULL((select score from user_score_info b where b.s_name = a.s_name and c_name="c#"),0) cc,

IFNULL((select score from user_score_info b where b.s_name = a.s_name and c_name="php") ,0) php from user_score_info a;

2、列转行：【主要用到union】

sql脚本

-- 主要使用union实现的

select user_name ,"java" course,java_SCORE score from test_tb_grade2

UNION select user_name ,"c#" course,c_score score from test_tb_grade2

UNION select user_name ,"php" course,php_SCORE score from test_tb_grade2

order by user_name,course

select user_name ,"java" course,java_SCORE score from test_tb_grade2

UNION all select user_name ,"c#" course,c_score score from test_tb_grade2

UNION all select user_name ,"php" course,php_SCORE score from test_tb_grade2

order by user_name,course