常见的行列转换包括以下四种情况：

1.列转行

2.行转列

3.列转换成字符串

4.字符串转换成列

1.列转行

导入数据

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `course` varchar(50) DEFAULT NULL COMMENT '课程',
  `score` int(3) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `t_student` VALUES (1,'张三', '语文', 95);
INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
INSERT INTO `t_student` VALUES (9,'王五', '英语', 87);
INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);

t_student表 (学生成绩表)

1.1 MAX(CASE WEHN)方法

SELECT name as '姓名',
       MAX(CASE WHEN course = '语文' THEN score END) AS '语文',
       MAX(CASE WHEN course = '数学' THEN score END) AS '数学',
       MAX(CASE WHEN course = '英语' THEN score END) AS '英语',
       MAX(CASE WHEN course = '历史' THEN score END) AS '历史'
FROM t_student
GROUP BY name;

结果展示:

涉及知识点: CASE表达式 | 聚合函数

1.2 SUM(IF(条件,列值,0))

SELECT name as '姓名',
       SUM(IF(course = '语文',score,0)) AS '语文',
       SUM(IF(course = '数学',score,0)) AS '数学',
       SUM(IF(course = '英语',score,0)) AS '英语',
       SUM(IF(course = '历史',score,0)) AS '历史'
FROM t_student
GROUP BY name;

结果展示:

涉及知识点: IF函数

2.行转列

导入数据

DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `chinese` double DEFAULT NULL COMMENT '语文成绩',
  `math` double DEFAULT NULL COMMENT '数学成绩',
  `english` double DEFAULT NULL COMMENT '英语成绩',
  `history` double DEFAULT NULL COMMENT '历史成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO t_course VALUES ('1', '张三', '95', '86', '78', '98');
INSERT INTO t_course VALUES ('2', '李四', '99', '96', '88', '85');
INSERT INTO t_course VALUES ('3', '王五', '80', '81', '87', '89');

t_course表

行转列的过程， 其实就是列转行的逆过程

-- 列转行:通过UNION或UNION ALL实现
SELECT user_name,'语文' AS course,chinese AS score FROM t_course
UNION ALL
SELECT user_name,'数学' AS course,math AS score FROM t_course
UNION ALL
SELECT user_name,'英语' AS course,english AS score FROM t_course
UNION ALL
SELECT user_name,'政治' AS course,history AS score FROM t_course
ORDER BY user_name;

部分结果展示:

涉及知识点: 组合查询

UNION 与 UNION ALL的区别:

1.对重复结果的处理: UNION会去掉重复记录，UNION ALL不会

2.对排序的处理: UNION会排序，UNION ALL只是简单地将两个结果集合并

3.效率方面的区别: 因为UNION会做去重和排序处理，因此效率比UNION ALL慢很多

3.列转换成字符串

在某些场景下，我们可能会对单列或者多列转换成字符串，实现这个需求需要使用到 GROUP_CONCAT函数

语法格式

GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

导入数据

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `course` varchar(50) DEFAULT NULL COMMENT '课程',
  `score` int(3) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `t_student` VALUES (1,'张三', '语文', 95);
INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
INSERT INTO `t_student` VALUES (9,'王五', '英语', 87);
INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);

t_student表 (学生成绩表)

问题: 实现t_student表中课程和成绩拼接为一个字符串的功能

SELECT name, GROUP_CONCAT(course, ":", score) AS '课程:成绩'
FROM t_student
GROUP BY name;

结果展示:

涉及知识点: GROUP_CONCAT函数

4.字符串转换成列

在某些场景下，我们需要把某一列的字符串转成多列

导入数据

t_user_order表 (用户订单表)

DROP TABLE IF EXISTS `t_user_order`;
CREATE TABLE `t_user_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `user_id` varchar(50) DEFAULT NULL COMMENT '用户 id',
  `order_id` varchar(100) DEFAULT NULL COMMENT '订单 ids',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO t_user_order VALUES ('1', 'user1', '1,3,5,19,20');
INSERT INTO t_user_order VALUES ('2', 'user2', '2,4,6,8,30,50');
INSERT INTO t_user_order VALUES ('3', 'user3', '11,15,29,31,33');

结果展示:

从上表可以看出用户ID (user_id)和订单ID (order_id)之间的关系是一对多关系,用户ID对应的订单 ID是一个字符串

问题:  将order_id中的字符串转换成列

思路: 利用help_topic表把以逗号分隔的字符串转换成行

-- 字符串转换成列: 利用SUBSTRING_INDEX和mysql.help_topic实现
SELECT a.user_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_id, ',', b.help_topic_id + 1 ), ',',- 1 )AS order_id
FROM t_user_order AS a
LEFT JOIN mysql.help_topic AS b 
ON b.help_topic_id < (length(a.order_id) - length(REPLACE(a.order_id, ',', '' )) + 1);

部分结果展示:

涉及知识点: SUBSTRING函数 | SUBSTRING_INDEX函数

sum函数不止可以求和，还可以行列转换

表数据（一）

一、行列转换(一)

写法一：

select date_format(game_date, '%Y-%m-%d') 时间,
		sum(if(Win_Lose='胜',1,0)) 胜,
		sum(if(Win_Lose='负',1,0)) 负
from jg
group by game_date;

写法二：

SELECT date_format(game_date, '%Y-%m-%d') AS 时间
	, SUM(if(Win_Lose = '胜', 1, 0)) AS 胜
	, SUM(if(Win_Lose = '负', 1, 0)) AS 负
FROM jg
GROUP BY game_date;

执行原理：1.先FROM jg
2.GROUP BY game_date
3.SQL聚集函数:SUM()

二、行列转换(二)

表数据：

User ID(用户ID)、Customer Service ID（客服ID）、Evaluation ID（评价ID）、Evaluation category（评价类别）、Rating stars（评价星级）

行列转化，把评价类别Evaluation category转化为列，最后找到符合物流=5，服务=5，质量=5的评价id

SELECT User_ID, Customer_Service_ID
	, SUM(if(Rating_stars = '物流', 5, NULL)) AS 物流
	, SUM(if(Rating_stars = '服务', 5, NULL)) AS 服务
	, SUM(if(Rating_stars = '质量', 5, NULL)) AS 质量
FROM Customer_Service_data
GROUP BY User_ID, Customer_Service_ID;

1.每个通告的已报名、已确认、已通过人数

SELECT res.*,
                     count(DISTINCT case when u.user_status = 'YQR' then u.pair_id end) yqr,
                     count(DISTINCT case when u.user_status = 'YBM' then u.pair_id end) ybm,
                     count(DISTINCT case when u.user_status = 'YTG' then u.pair_id end) ytg
              from (SELECT title, act_id, DATE_FORMAT(create_time, '%Y-%m-%d') create_time
                    FROM activity
                   ) res
                       LEFT JOIN activity_user u on res.act_id = u.act_id
              GROUP BY res.act_id, u.user_status

2.运行结果