mysql 行转列的实际运用

需要进行行转列的场景：

姓名	打怪数
猪八戒	10
猪八戒	2
猪八戒	12
沙僧	3
沙僧	5
沙僧	1
熏悟空	20
熏悟空	10
熏悟空	17

熏悟空	猪八戒	沙僧
47	24	9

1. 使用cross join
   效率不高，随着人数增加sql越来越复杂。

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

2. 使用case-when

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