MySql行转列

题目

学习视频

转为

环境

create table SQL_1
(
	name varchar(20),
	subject varchar(20),
	score float
);

insert into SQL_1(name, `subject`, score) values ('张三', '语文', 78);
insert into SQL_1(name, `subject`, score) values ('张三', '数学', 68);
insert into SQL_1(name, `subject`, score) values ('张三', '英语', 48);
insert into SQL_1(name, `subject`, score) values ('李四', '语文', 78);
insert into SQL_1(name, `subject`, score) values ('李四', '数学', 88);
insert into SQL_1(name, `subject`, score) values ('李四', '英语', 98);
insert into SQL_1(name, `subject`, score) values ('王五', '语文', 28);
insert into SQL_1(name, `subject`, score) values ('王五', '数学', 38);
insert into SQL_1(name, `subject`, score) values ('王五', '英语', 18);

解题步骤

• 确定分组列，转换列，数据列
• 生成位列
• 做分组查询
• 选择合适的聚合函数

两步法

公式：

select 分组列,
	聚合函数(m1) as 列名1,
	聚合函数(m2) as 列名2
	聚合函数(m3) as 列名3
from (
select*，
	case 转换列 when 转换列值1 then 数据列 else ... end as m1,
	case 转换列 when 转换列值2 then 数据列 else ... end as m2,
	case 转换列 when 转换列值3 then 数据列 else ... end as m3, from 表名
) 临时表名
group by 分组列;

解题SQL
第一步：

select *,
	case subject when '语文' then score else 0 end as m1,
	case subject when '数学' then score else 0 end as m2,
	case subject when '英语' then score else 0 end as m3
from sql_1

第二步：

select name,
	sum(m1) as 语文,
	sum(m2) as 数学,
	sum(m3) as 英语
from 
(
	select *,
		case subject when '语文' then score else 0 end as m1,
		case subject when '数学' then score else 0 end as m2,
		case subject when '英语' then score else 0 end as m3
	from sql_1
) as tmp
group by name; 

一步法：

select 分组列，
	聚合函数(case 转换列 when 转换列值1 then 数据列 else ...end) as 列名1,
	聚合函数(case 转换列 when 转换列值2 then 数据列 else ...end) as 列名2,
    聚合函数(case 转换列 when 转换列值3 then 数据列 else ...end) as 列名3
    ...
from 表名
group by 分组列;

select 分组列，
	聚合函数(case when 转换列 = 转换列值1 then 数据列 else ...end) as 列名1,
	聚合函数(case when 转换列 = 转换列值2 then 数据列 else ...end) as 列名2,
	聚合函数(case when 转换列 = 转换列值3 then 数据列 else ...end) as 列名3
	...
from 表名
group by 分组列;

题解SQL

# 当subject 为语文的时候返回score,否则返回0（也可以返回NULL表示没有这个记录）
select name,
	sum(case when `subject` = '语文' then score else 0 end) as 语文,
	sum(case when `subject` = '数学' then score else 0 end) as 数学,
	sum(case when `subject` = '英语' then score else 0 end) as 英语
from SQL_1
group by name;

select name,
	sum(case `subject` when '语文' then score else 0 end) as 语文,
	sum(case `subject` when '数学' then score else 0 end) as 数学,
	sum(case `subject` when '英语' then score else 0 end) as 英语
from SQL_1
group by name;

课后练习