最近在慕課上 看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;新聞熱點
疑難解答
