【MySQL】数据表行列转换

 系列文章

C#底层库--MySQL脚本自动构建类（insert、update语句生成）
本文链接：C#底层库--MySQL脚本自动构建类（insert、update语句生成）_c# mysql_花北城的博客-CSDN博客

C#底层库--MySQL数据库访问操作辅助类（推荐阅读）
本文链接：C#底层库--MySQL数据库访问操作辅助类（推荐阅读）_mysql安装教程_花北城的博客-CSDN博客

C#底层库--SQLiteHelper访问操作辅助类
本文链接：C#底层库--SQLiteHelper帮助类_花北城的博客-CSDN博客

提高编程效率--数据导入工具
本文链接：C#提高编程效率专辑—数据导入工具_c# 导表工具_花北城的博客-CSDN博客

Oracle数据库限制ip访问
本文链接：数据库--Oracle限制某些ip访问_oracle限制ip访问_花北城的博客-CSDN博客

SQL 获取数据库表，指定字段并且判断是否为主键
本文链接：SQL 获取数据库表，指定字段并且判断是否为主键_花北城的博客-CSDN博客

SQL outer apply的用法
本文链接：SQL outer apply的用法_花北城的博客-CSDN博客

MySQL安装教程（详细）
本文链接：MySQL安装教程（详细）_花北城的博客-CSDN博客

MySQL卸载教程（详细）
本文链接：MySQL卸载教程（详细）_花北城的博客-CSDN博客

MySQL分类汇总(group by...with rollup)，如何显示“总计”字段?
本文链接：MySQL分类汇总(group by...with rollup)，如何显示“总计”字段?_sql 分类汇总_花北城的博客-CSDN博客

MySQL WITH CHECK OPTION的用法
本文链接：MySQL WITH CHECK OPTION的用法_花北城的博客-CSDN博客

MySQL 使用存储过程插入千万级数据如何提升效率？
本文链接：MySQL使用存储过程插入千万级数据如何提升效率？_存储过程插数效率_花北城的博客-CSDN博客

【MySQL】数据表行列转换
本文链接：MySQL 数据库表行列转置的实现_花北城的博客-CSDN博客

前言

本专栏为【数据库】，主要介绍SQL的功能与特点、SQL数据定义语言（表、视图、索引、约束）、SQL数据操作语言（数据检索、数据插入、数据删除、数据更新）、创建与删除触发器、SQL数据控制语言（安全性和授权、事务处理）以及嵌入式SQL。
如果你对本专辑感兴趣，持续关注吧。大家有任何问题，也可以评论区反馈，私信我。
 

一、技术介绍

数据表行列转换，顾明思议，将表格中一行行的数据，采用SQL脚本转换成列数据。

二、需求描述

​

写出查询语句，使最后的输出结果为：

二、测试用例

2.1 创建分数表 t_score

DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score`  (
  `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `subject` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
  `score` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分数',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

2.2 初始化数据

-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES ('张三', '语文', '80', 1);
INSERT INTO `t_score` VALUES ('张三', '数学', '10', 2);
INSERT INTO `t_score` VALUES ('张三', '英语', '70', 3);
INSERT INTO `t_score` VALUES ('张三', '生物', '77', 4);
INSERT INTO `t_score` VALUES ('张三', '化学', '45', 5);
INSERT INTO `t_score` VALUES ('奥德彪', '语文', '19', 6);
INSERT INTO `t_score` VALUES ('奥德彪', '数学', '99', 7);
INSERT INTO `t_score` VALUES ('奥德彪', '英语', '100', 8);
INSERT INTO `t_score` VALUES ('奥德彪', '生物', '86', 9);
INSERT INTO `t_score` VALUES ('奥德彪', '化学', '72', 10);

2.3 数据查询

正确的SQL语句为：

SELECT NAME,
MAX(CASE SUBJECT WHEN '语文' THEN score END) AS '语文',
MAX(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' ,
MAX(CASE SUBJECT WHEN '英语' THEN score END) AS '英语' ,
MAX(CASE SUBJECT WHEN '生物' THEN score END) AS '生物' ,
MAX(CASE SUBJECT WHEN '化学' THEN score END) AS '化学' 
FROM t_score GROUP BY NAME; 

MIN、SUM都是可行的，就是聚合函数都可以，它们之间存在着什么特殊的关系吗？
 

2.4 去掉第一聚合函数，数据查询

SELECT NAME,
(CASE SUBJECT WHEN '语文' THEN score END) AS '语文',
MAX(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' ,
MAX(CASE SUBJECT WHEN '英语' THEN score END) AS '英语' ,
MAX(CASE SUBJECT WHEN '生物' THEN score END) AS '生物' ,
MAX(CASE SUBJECT WHEN '化学' THEN score END) AS '化学' 
FROM t_score GROUP BY NAME; 

发现去掉第一case中的MAX并不影响结果：

2.4 去掉第二聚合函数，数据查询

SELECT NAME,
(CASE SUBJECT WHEN '语文' THEN score END) AS '语文',
(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' ,
MAX(CASE SUBJECT WHEN '英语' THEN score END) AS '英语' ,
MAX(CASE SUBJECT WHEN '生物' THEN score END) AS '生物' ,
MAX(CASE SUBJECT WHEN '化学' THEN score END) AS '化学' 
FROM t_score GROUP BY NAME; 

去掉第二个结果就变了，这是为什么？

2.5 依次数据查询 

再搞清楚这个问题前，我们先看一下case的用法，先化整体为单一开始分析
1、SELECT NAME,
(CASE SUBJECT WHEN '语文' THEN score END) AS '语文'
FROM t_score 

 2、SELECT NAME,
(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' 
FROM t_score 

3、SELECT NAME,
MAX(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' 
FROM t_score GROUP BY NAME

 

2.6 增加“总计”字段，数据查询 

方法一：

SELECT IFNULL(NAME,'小计') AS NAME,
MAX(CASE SUBJECT WHEN '语文' THEN score END) AS '语文',
MAX(CASE SUBJECT WHEN '数学' THEN score END) AS '数学' ,
MAX(CASE SUBJECT WHEN '英语' THEN score END) AS '英语' ,
MAX(CASE SUBJECT WHEN '生物' THEN score END) AS '生物' ,
MAX(CASE SUBJECT WHEN '化学' THEN score END) AS '化学' ,
SUM(score) '总计'
FROM t_score GROUP BY NAME
with ROLLUP

方法二：

SELECT IFNULL(NAME,'小计') AS NAME,
SUM(IF(SUBJECT='语文',score,0)) AS 语文,
SUM(IF(SUBJECT='数学',score,0)) AS 数学,
SUM(IF(SUBJECT='英语',score,0)) AS 英语,
SUM(IF(SUBJECT='生物',score,0)) AS 生物,
SUM(IF(SUBJECT='化学',score,0)) AS 化学,
SUM(score) AS '总计' 
FROM t_score GROUP BY NAME
WITH ROLLUP;

查询结果：

 

 
三、技术总结

3.1 Select查询

通过对比可以发现，恰恰是第一个case（不加函数的）第一条存在数据，所以导致case then的结果正确，而其他的case第一条数据都为空。查阅资料我们可以知道case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。所以正是因为这个原因导致结果不同。建议上面所有case加上MAX（）函数，因为通过MAX（）函数可以过滤到匹配到空值的情况

3.2 存储过程

以上查询的方式，有局限性，必须要手动一个个列举表格行数据的值，有没有动态的方法，就是不要一个个列举“课程”，因为显然在不确定表格数据的情况下，很有必要。

答案是有的，可以采用存储过程拼接SQL语句的形式实现：

SET @EE='';
select @EE :=CONCAT(@EE,'SUM(IF(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM t_score) A ;

 
SET @QQ = CONCAT('select IFNULL(NAME,\'小计\')as NAME,',@EE,' SUM(score) as \'总计\' from t_score GROUP BY NAME WITH ROLLUP'); SELECT @QQ;
 
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;