sql之行列转化

1、行转列

    假设表courses内容如下：

1.PNG

    需求：将表courses转化成交叉表，即

3.PNG

select 
c.`name`,
case
when sum(if(c.course='SQL入门',1,0))=1 then 'o' else 'x' end as 'SQL入门',
case
when sum(if(c.course='unix基础',1,0))=1 then 'o' else 'x' end as 'unix基础',
case
when sum(if(c.course='Java中级',1,0))=1 then 'o' else 'x' end as 'Java中级'
from courses c
GROUP BY c.`name`

    法二：多个left join

select
c0.name,
if(c1.name is not null,'o','x') as 'SQL入门',
if(c2.name is not null,'o','x') as 'unix基础',
if(c3.name is not null,'o','x') as 'Java中级'
from (select distinct name from courses) c0
left join
(select name from courses where course='SQL入门') c1
on c0.name=c1.name
left join
(select name from courses where course='unix基础') c2
on c0.name=c2.name
left join
(select name from courses where course='Java中级') c3
on c0.name=c3.name

    法三：使用标量子查询

select
c0.name,
(select 'o' from courses c1 where c0.name=c1.name and c1.course='SQL入门') as 'SQL入门',
(select 'o' from courses c2 where c0.name=c2.name and c2.course='unix基础') as 'unix基础',
(select 'o' from courses c3 where c0.name=c3.name and c3.course='Java中级') as 'Java中级'
from (select distinct name from courses) c0

法二运用了大量关联和临时表，比较耗性能；法三在select中使用关联子查询，比较耗性能。

2、列转行

    假设表personnel内容如下：

4.PNG

    需求：将表personnel修改如下

6.PNG

    法一：union

select
p1.employee,p1.child_1
from personnel p1
union all
select 
p2.employee,p2.child_2
from personnel p2
union all
select 
p3.employee,p3.child_3
from personnel p3

    返回

5.PNG

    union all不会清除重复值
    将union all改成union的话【工藤，null的这条记录还在，不符合需求】

image.png

    法二：临时视图+left join【符合需求】

create view child_view(child)
as
select child_1 from personnel where child_1<>''
union 
select child_2 from personnel where child_2<>''
union 
select child_3 from personnel where child_3<>'';
select
p1.employee,v1.child
from personnel p1
left join
child_view v1
on v1.child in (p1.child_1,p1.child_2,p1.child_3);

    返回

6.PNG

以上内容来自《SQL进阶教程》