MySQL开发技巧（二）

课程地址：https://www.imooc.com/learn/427

第1章课程介绍

1-1 MySQL开发技巧（二）课程介绍

第2章如何进行行列转换

2-1 MySQL-行转列场景介绍

2-2 MySQL-需求分析

2-3 MySQL-使用自连接的方法实现行转列

select a.`user_name`,kills from user1 a join user_kills b on a.id=b.user_id;

select a.`user_name`,sum(kills) from user1 a join user_kills b on a.id=b.user_id group by a.user_name;

select a.`user_name`,sum(kills) from user1 a join user_kills b on a.id=b.user_id group by a.user_name='孙悟空';

select * from (
  select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id=b.user_id group by a.user_name='孙悟空';
) a cross join (
    select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id=b.user_id group by a.user_name='猪八戒';
) b cross join (
    select sum(kills) as '沙僧' from user1 a join user_kills b on a.id=b.user_id group by a.user_name='沙僧';
) c

2-4 MySQL-使用CASE方法实现行转列

select a.`user_name`,kills from user1 a join user_kills b on a.id=b.user_id;

select sum(case when user_name='孙悟空' then kill end) as '孙悟空',
  sum(case when user_name='猪八戒' then kill end) as '猪八戒',
  sum(case when user_name='沙僧' then kill end) as '沙僧'
 from user1 a join user_kills b on a.id = b.user_id;

2-5 MySQL-单列转多行场景介绍

2-6 MySQL-使用序列化表的方法实现行转列

2-7 MySQL-使用UNION的方法实现列转行

select user_name,'arms' as equipment,arms from user1 a join user1_equipment b on a.id=b.user_id
union all
select user_name,'clothing' as equipment,clothing from user1 a join user1_equipment b on a.id=b.user_id
union all
select user_name,'shoe' as equipment,shoe from user1 a join user1_equipment b on a.id=b.user_id
order by user_name;

2-8 MySQL-使用序列化表的方法实现列转行

select user_name,
case when c.id = 1 then 'arms'
     when c.id = 2 then 'clothing'
     when c.id = 3 then 'shoe'
end as equipment
,coalesce(case when c.id = 1 then arms end
,case when c.id = 2 then clothing end
,case when c.id = 3 then shoe end) as eq_name
from user1 a
join user1_equipment b on a.id = b.user_id
cross join tb_sequence c where c.id <= 3 order by user_name;