本文演示使用sql进行列转行的操作和列转行

一、行转列

创建表

drop table score_1;CREATE TABLE IF NOT EXISTS score_1(    `user_id` BIGINT NOT NULL COMMENT "user_id",    `subject` VARCHAR(255) NOT NULL COMMENT "学科",    `score` BIGINT  NOT NULL DEFAULT '0' COMMENT "分数")DUPLICATE KEY(`user_id`,`subject`,`score`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");desc  score_1;

 插入数据

 insert  into `score_1`(`user_id`,`subject`,`score`) values (1,'MATH',90),(1,'ENGLISH',98),(1,'CHINESE',85),(2,'MATH',87),(2,'ENGLISH',78),(2,'CHINESE',89), (3,'MATH',90),(3,'ENGLISH',98),(3,'CHINESE',85),(4,'MATH',87),(4,'ENGLISH',78),(4,'CHINESE',89);

行转列的三种方式
1、union all

select user_id,SUM(MATH) AS 'MATH',SUM(ENGLISH) AS 'ENGLISH',SUM(CHINESE)AS CHINESE from ((SELECT user_id,score AS 'MATH',0 AS 'ENGLISH',0 AS 'CHINESE' FROM score_1 WHERE subject = 'MATH')UNION ALL(SELECT user_id,0 AS 'MATH',score AS 'ENGLISH',0 AS 'CHINESE' FROM score_1 WHERE subject = 'ENGLISH')UNION ALL(SELECT user_id,0 AS 'MATH',0 AS 'ENGLISH',score AS 'CHINESE' FROM score_1 WHERE subject = 'CHINESE')) tGROUP BY user_id

2、join

SELECT t1.user_id, t1.MATH, t2.score AS 'ENGLISH',t3.score AS 'CHINESE' FROM    ( SELECT user_id, score AS 'MATH' FROM score_1 WHERE subject = 'MATH' ) AS t1    JOIN ( SELECT user_id, score FROM score_1 WHERE subject = 'ENGLISH' ) AS t2 ON t1.user_id = t2.user_id    JOIN ( SELECT user_id, score FROM score_1 WHERE subject = 'CHINESE' ) AS t3 ON t1.user_id = t3.user_id

3、sum group

    SELECT user_id,        sum(if(subject='MATH', score, NULL)) as `MATH`,        sum(if(subject='ENGLISH', score, NULL)) as `ENGLISH`,       sum(if(subject='CHINESE', score, NULL)) as `CHINESE`FROM score_1 GROUP BY user_id

二、列转行

drop table score_2;CREATE TABLE IF NOT EXISTS score_2(    `user_id` BIGINT NOT NULL COMMENT "user_id",    `math_score` BIGINT NOT NULL COMMENT "数学分数",    `en_score` BIGINT NOT NULL COMMENT "英语分数",    `cn_score` BIGINT NOT NULL COMMENT "语文分数")UNIQUE KEY(`user_id`)DISTRIBUTED BY HASH(`user_id`) BUCKETS 1PROPERTIES ("replication_allocation" = "tag.location.default: 1");INSERT INTO score_2(user_id,cn_score,math_score,en_score) VALUES (1,90,92,80),(2,91,91,82),(3,97,81,86),(4,66,87,68),(5,86,89,78),(6,85,82,45);select * from score_2;select user_id,'语文' as subject,cn_score as score from score_2unionselect user_id,'英语' as subject,en_score as score from score_2union select user_id,'数学' as subject,math_score as score from score_2;

来源地址：https://blog.csdn.net/rjbcxhc/article/details/128854546