MySQL 复杂查询-行转列和列转行 Demo

建表及插入数据

-- 创建表
drop table if EXISTS t_score;
create table t_score(
id int auto_increment primary key,
u_name varchar(32),
c_name varchar(32),
score double
);
-- 插入数据
insert into t_score(u_name, c_name, score) values
('张三', 'JavaSE', 80),
('张三', 'JDBC', 90),
('张三', 'Servlet', 85),
('李四', 'JavaSE', 70),
('李四', 'JDBC', 80),
('李四', 'Servlet', 80),
('王五', 'JavaSE', 90),
('王五', 'JDBC', 90),
('王五', 'Servlet', 60);

查找所有学生成绩信息

select * from t_score;

查询结果

行转列

使用多表连接的方式

-- 1. 使用多表连接查询的方式
select 
	t1.u_name, t2.score as 'JavaSE', t3.score as 'JDBC', t4.score as 'Servlet'
from t_score t1 
join (select u_name, score from t_score where c_name = 'JavaSE')t2  on t2.u_name = t1.u_name
join (select u_name, score from t_score where c_name = 'JDBC')t3  on t3.u_name = t3.u_name
join (select u_name, score from t_score where c_name = 'Servlet')t4  on t4.u_name = t4.u_name
group by t1.u_name;

使用 MySQL 中 case when then else end 结构

-- 2. 使用 case when 条件 then 字段值 else 值 end 
select u_name
,sum(case when c_name = 'JavaSE' then score else 0 end ) as 'JavaSE'
,sum(case when c_name = 'JDBC' then score else 0 end ) as 'JDBC'
,sum(case when c_name = 'Servlet' then score else 0 end ) as 'Servlet'
from t_score 
group by u_name;

执行 SQL 语句，结果如下：

列转行

建表及导入数据

-- 创建表 
create table t_score_row(
	id int(10) primary key auto_increment,
	u_name varchar(255) not null,
	JavaSE int(3),
	JDBC int(3),
	Servlet int(3)
);
-- 导入数据
insert into t_score_row(u_name,JavaSE,JDBC,Servlet) select u_name
,sum(case when c_name = 'JavaSE' then score else 0 end ) as 'JavaSE'
,sum(case when c_name = 'JDBC' then score else 0 end ) as 'JDBC'
,sum(case when c_name = 'Servlet' then score else 0 end ) as 'Servlet' 
from t_score 
group by u_name;

查询结果为：

使用 union all 实现列转行

select u_name,'JavaSE' as c_name,JavaSE as 'score' from t_score_row
union all 
select u_name,'JDBC' as c_name,JavaSE as 'JDBC' from t_score_row
union all
select u_name,'Servlet' as c_name,JavaSE as 'Servlet' from t_score_row

执行 SQL ，运行结果如下：