系列文章
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;