sql进行行列转换

1.行列转换

1.分别求出孙悟空、猪八戒、沙僧的总打怪数

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

select sum(kills) from user1 a join user_kills b on a.id=b.user_id where a.user_name='猪八戒' ;

select sum(kills) from user1 a join user_kills b on a.id=b.user_id where a.user_name='沙僧' ;

 

2.是从cross join的方式关联上述3个表的结果

select * from

(select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id=b.user_id where a.user_name='孙悟空' ) a

cross join

(select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id=b.user_id where a.user_name='猪八戒') b

cross join

(select sum(kills) as '沙僧' from user1 a join user_kills b on a.id=b.user_id where a.user_name='沙僧') c

------------------------------------------------------------------------

select * from
(select sum(hour) as '张三' from teachers a join teacherdate b on a.name=b.name where a.name='张三' ) a
cross join
(select sum(hour) as '李四' from teachers a join teacherdate b on a.name=b.name where a.name='李四') b
cross join
(select sum(hour) as '王五' from teachers a join teacherdate b on a.name=b.name where a.name='王五') c

3.使用case的方式实现

select
    sum(case when b.name='张三' then b.hour end) as '张三',
    sum(case when b.name='李四' then b.hour end) as '李四',
    sum(case when b.name='王五' then b.hour end) as '王五'
 from teachers a join teacherdate b on a.name=b.name

 

 

2.列转行