mysql 行列转换，行转列和列转行

た入场券 2024-04-18 19:58 76阅读 0赞

**目录**

行转列

列转行

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

## 行转列 ##

建立测试表，添加测试数据

DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score` (
      `subject` varchar(255) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      `stu_id` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of score
    -- ----------------------------
    INSERT INTO `score` VALUES ('语文', '88', '101');
    INSERT INTO `score` VALUES ('语文', '90', '2');
    INSERT INTO `score` VALUES ('语文', '78', '3');
    INSERT INTO `score` VALUES ('数学', '93', '3');
    INSERT INTO `score` VALUES ('数学', '89', '101');
    INSERT INTO `score` VALUES ('数学', '98', '2');
    INSERT INTO `score` VALUES ('英语', '99', '2');
    INSERT INTO `score` VALUES ('英语', '69', '3');
    INSERT INTO `score` VALUES ('英语', '96', '101');
    INSERT INTO `score` VALUES ('英语', '99', '4');

表数据结构如图：

![20190911135638778.png][]

效果图：

![20190911135709669.png][]

1、使用case when

SELECT stu_id,
    sum(case when subject='语文' then score else 0 end) as '语文',
    sum(case when subject='数学' then score else 0 end) as '数学',
    sum(case when subject='英语' then score else 0 end) as '英语'
     FROM score GROUP BY stu_id;

2、使用if

SELECT stu_id,
    sum(if(subject='语文',score,0 )) as '语文',
    sum(if(subject='数学',score,0 )) as '数学',
    sum(if(subject='英语',score,0 )) as '英语'
     FROM score GROUP BY stu_id;

## 列转行 ##

建立测试表，添加测试数据

DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score` (
      `stu_id` int(11) DEFAULT NULL,
      `chinese_score` int(11) DEFAULT NULL,
      `math_score` int(11) DEFAULT NULL,
      `english_score` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of score
    -- ----------------------------
    INSERT INTO `score` VALUES ('101', '80', '90', '100');
    INSERT INTO `score` VALUES ('102', '88', '78', '99');
    INSERT INTO `score` VALUES ('103', '69', '98', '87');

表数据结构如图：

![20191205165332660.png][]

列转行sql：

SELECT stu_id,'语文' AS course,chinese_score AS score FROM score
    UNION ALL
    SELECT stu_id,'数学' AS course,math_score AS score FROM score
    UNION ALL
    SELECT stu_id,'外语' AS course,english_score AS score FROM score
    ORDER BY stu_id

转换效果：

![20191205165959790.png][]

[20190911135638778.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/92541ce91da94285bfcde13fa1595e13.png
[20190911135709669.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/8c8b50c90f224512a5bc1aaa7e4f1654.png
[20191205165332660.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b8efd15687734a179305a42481f811e4.png
[20191205165959790.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/5fac6565d2824d448a5265d73b54dd2d.png