MySQL 行列转换

2019-11-08 20:38:33

字体：大中小

来源：转载

供稿：网友

最近在慕课上看MySQL教程里面关于行转列的教程不错贴上练习SQL 做个记录简单行转列

SELECT a.user_name, sum(b.kills)FROM user1 aJOIN user_kills b ON a.id = b.user_idGROUP BY user_name;

CROSS JOIN 行列转换

select * from ( SELECT sum(kills) AS '孙悟空' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '孙悟空') aCROSS JOIN ( SELECT sum(kills) AS '沙甥' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '沙甥') bCROSS JOIN ( SELECT sum(kills) AS '猪八戒' FROM user1 a JOIN user_kills b ON a.id = b.user_id AND a.user_name = '猪八戒') c;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 user1 aJOIN user_kills b ON a.id = b.user_id;

单列转多行

SELECT user_name, REPLACE ( substring( substring_index(mobile, ',', a.id), CHAR_LENGTH( SUBSTRING_INDEX(mobile, ',', a.id - 1) ) + 1 ), ',', '' ) AS mobileFROM tb_sequence aCROSS JOIN ( SELECT user_name, CONCAT(mobile, ',') AS mobile, LENGTH(mobile) - LENGTH(REPLACE(mobile, ',', '')) + 1 size FROM user1 b) b ON a.id <= b.size;

上一篇：MySQL删除重复数据并保留ID最大值

下一篇：oracle sql语法优化