当前位置 : 主页 > 数据库 > mysql >

mysql如何实现行转列

来源:互联网 收集:自由互联 发布时间:2021-08-19
mysql实现行转列的方法:1、用cross join的方式实现,代码为【cross join(select sum(a.kills) 猪八戒】;2、用case语句的方式实现,代码为【case when user_name=孙悟空】。 本教程操作环境:windows7系

mysql实现行转列的方法:1、用cross join的方式实现,代码为【cross join(select sum(a.kills) '猪八戒】;2、用case语句的方式实现,代码为【case when user_name='孙悟空'】。

本教程操作环境:windows7系统、mysql8.0.22版,该方法适用于所有品牌电脑。

mysql实现行转列的方法:

1、用cross join的方式实现

select * from 
(select sum(a.kills) '孙悟空' 
  from kills1 as a 
  LEFT JOIN tssrz as b 
    on a.user_id = b.id 
 WHERE b.user_name = '孙悟空' 
 GROUP BY b.user_name) e
 cross join 
 (select sum(a.kills) '猪八戒' 
  from kills1 as a 
  LEFT JOIN tssrz as b 
    on a.user_id = b.id 
 WHERE b.user_name = '猪八戒' 
 GROUP BY b.user_name) f

2、用case语句的方式实现

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 tssrz as a
   join kills1 as b
     on a.id = b.user_id

推荐:mysql视频教程

以上就是mysql如何实现行转列的详细内容,更多请关注自由互联其它相关文章!

网友评论