MySql行转列展示学生成绩更加简短的写法

目录

前言

更加简单的写法

SQL

---

前言

对于这种行转列我想大家也不陌生

转成

通常在mysql中实现行转列的方式是这样的：

SELECT
	student_name,
	MAX(CASE WHEN subject_name = '语文' THEN grade ELSE '' END) AS '语文',
	MAX(CASE WHEN subject_name = '数学' THEN grade ELSE '' END) AS '数学',
	MAX(CASE WHEN subject_name = '英语' THEN grade ELSE '' END) AS '英语' 
FROM
	`score` 
GROUP BY
	student_name;

而我的方式是这样的（一开始认为所有的聚合函数都不能适用于字符串的行转列所研究出来的奇怪写法）

SELECT
	student_name,
	GROUP_CONCAT( IF ( subject_name = '语文', grade, '' ) SEPARATOR '' ) AS '语文',
	GROUP_CONCAT( IF ( subject_name = '数学', grade, '' ) SEPARATOR '' ) AS '数学',
	GROUP_CONCAT( IF ( subject_name = '英语', grade, '' ) SEPARATOR '' ) AS '英语' 
FROM
	`score` 
GROUP BY
	student_name;

GROUP_CONCAT函数用于把分组后每一个组的值连接起来，加入if条件判断，然后把分隔符设置为空。就能得到一个完整的行转列后的表格。

但是在我边运行sql边写文章的时候我才发现原来MAX这个聚合函数也是可以用于对字符串的行转列操作。举一反三，因此我得出了一个更加简短的写法。

更加简单的写法

SELECT
	student_name,
	MAX( IF ( subject_name = '语文', grade, '' )) AS '语文',
	MAX( IF ( subject_name = '数学', grade, '' )) AS '数学',
	MAX( IF ( subject_name = '英语', grade, '' )) AS '英语' 
FROM
	`score` 
GROUP BY
	student_name

这个IF顾名思义就是一个条件判断。第一个参数是条件，第二个参数是条件成立后的结果，第三个是条件不成立后的结果。

SQL

表结构：
 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_name` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `student_name` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '语文', '皆非1号', '优');
INSERT INTO `score` VALUES (2, '英语', '皆非1号', '良');
INSERT INTO `score` VALUES (3, '数学', '皆非1号', '良');
INSERT INTO `score` VALUES (4, '语文', '皆非3号', '良');
INSERT INTO `score` VALUES (5, '英语', '皆非3号', '优');
INSERT INTO `score` VALUES (6, '英语', '皆非5号', '差');
INSERT INTO `score` VALUES (7, '数学', '皆非5号', '良');
INSERT INTO `score` VALUES (8, '语文', '皆非5号', '良');
INSERT INTO `score` VALUES (9, '语文', '皆非2号', '良');
INSERT INTO `score` VALUES (10, '英语', '皆非2号', '优');
INSERT INTO `score` VALUES (11, '数学', '皆非3号', '差');
INSERT INTO `score` VALUES (12, '数学', '皆非2号', '差');

SET FOREIGN_KEY_CHECKS = 1;

查询语句：

#常规写法
SELECT
	student_name,
	MAX( (CASE WHEN subject_name = '语文' THEN grade ELSE '' END)) AS '语文',
	MAX( (CASE WHEN subject_name = '数学' THEN grade ELSE '' END)) AS '数学',
	MAX( (CASE WHEN subject_name = '英语' THEN grade ELSE '' END)) AS '英语' 
FROM
	`score` 
GROUP BY
	student_name;
	
	
#我的写法
SELECT
	student_name,
	GROUP_CONCAT( IF ( subject_name = '语文', grade, '' ) SEPARATOR '' ) AS '语文',
	GROUP_CONCAT( IF ( subject_name = '数学', grade, '' ) SEPARATOR '' ) AS '数学',
	GROUP_CONCAT( IF ( subject_name = '英语', grade, '' ) SEPARATOR '' ) AS '英语' 
FROM
	`score` 
GROUP BY
	student_name;
	
#取长补短，更加简短的写法
SELECT
	student_name,
	MAX( IF ( subject_name = '语文', grade, '' )) '语文',
	MAX( IF ( subject_name = '数学', grade, '' )) '数学',
	MAX( IF ( subject_name = '英语', grade, '' )) '英语' 
FROM
	`score` 
GROUP BY
	student_name;