【SQL】MySQL 数据库的行列转换

  报表系统中经常需要行列转换，在 SQL Server 等数据库中可以用 PIVOT 、UNPIVOT 来实现，但是在 MySQL 数据库中却不支持，下面介绍 MySQL 中的行列转换的实现方法。

1.行转列

例如下面是数据库中的原始表格：

name	subject	score
张三	语文	94
张三	数学	100
张三	英语	90
李四	语文	93
李四	数学	91
李四	英语	99

我们需要得到下面的表格：

姓名	语文	数学	英语
张三	94	100	90
李四	93	91	99

在 Oracle 或者 SQL Server 中，我们可以通过下面的语句来实现：

select *
from student
pivot(
	sum(score) for subject in ('语文','数学','英语')
)

而在 MySQL 中可以用两种方法来实现：

• group by + case when 的语句来实现：

select
	name '姓名',
	max(case subject when '语文' then score else 0 end) as '语文',
	max(case subject when '数学' then score else 0 end) as '数学',
	max(case subject when '英语' then score else 0 end) as '英语'
from student
group by name;

• group by + if 的语句来实现：

select
	name '姓名',
	sum(if(subject='语文',score,0)) as '语文',
    sum(if(subject='数学',score,0)) as '数学',
    sum(if(subject='英语',score,0)) as '英语',
from student
group by name;

2.列转行

同样还是上面的两个表，现在需要将第二个表格转换为第一个表格。

在 Oracle 或者 SQL Server 中，我们可以通过下面的语句来实现：

select *
from student1
unpivot(
	score for subject in ('语文','数学','英语')
)

而在 MySQL 中可以用 group by + union 的语句来实现：

select
	name,
	'语文' as subject,
	max('语文') as score
from student1
group by name
union
select
	name,
	'数学' as subject,
	max('数学') as score
from student1
group by name
union
select
	name,
	'英语' as subject,
	max('英语') as score
from student1
group by name;

欢迎关注公众号。