mysql case when实现行转列查询

今天面试做了一道sql行转列，用子查询和 distinct关键字实现的，发现不好用。特意查了下资料，发现可以用 case when实现，特意记录下笔记
MySQL 的 case when 的语法有两种：

1. 简单函数
   CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

2. 搜索函数
   CASE WHEN [expr] THEN [result1]…ELSE [default] END

行转列测试数据

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 80016
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 80016
File Encoding         : 65001

Date: 2019-09-05 13:45:39
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for edu_courses
-- ----------------------------
DROP TABLE IF EXISTS `edu_courses`;
CREATE TABLE `edu_courses` (
  `course_no` varchar(20) NOT NULL COMMENT '课程编号',
  `course_name` varchar(100) NOT NULL COMMENT '课程名称',
  PRIMARY KEY (`course_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

-- ----------------------------
-- Records of edu_courses
-- ----------------------------
INSERT INTO `edu_courses` VALUES ('C001', '大学语文');
INSERT INTO `edu_courses` VALUES ('C002', '新视野英语');
INSERT INTO `edu_courses` VALUES ('C003', '离散数学');
INSERT INTO `edu_courses` VALUES ('C004', '概率论与数理统计');
INSERT INTO `edu_courses` VALUES ('C005', '线性代数');
INSERT INTO `edu_courses` VALUES ('C006', '高等数学');

-- ----------------------------
-- Table structure for edu_score
-- ----------------------------
DROP TABLE IF EXISTS `edu_score`;
CREATE TABLE `edu_score` (
  `stu_id` varchar(16) NOT NULL COMMENT '学号',
  `course_no` varchar(20) NOT NULL COMMENT '课程编号',
  `scores` float DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`stu_id`,`course_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';

-- ----------------------------
-- Records of edu_score
-- ----------------------------
INSERT INTO `edu_score` VALUES ('1001', 'C001', '67');
INSERT INTO `edu_score` VALUES ('1001', 'C002', '87');
INSERT INTO `edu_score` VALUES ('1001', 'C003', '83');
INSERT INTO `edu_score` VALUES ('1001', 'C004', '88');
INSERT INTO `edu_score` VALUES ('1001', 'C005', '77');
INSERT INTO `edu_score` VALUES ('1002', 'C001', '68');
INSERT INTO `edu_score` VALUES ('1002', 'C002', '88');
INSERT INTO `edu_score` VALUES ('1002', 'C003', '84');
INSERT INTO `edu_score` VALUES ('1002', 'C004', '89');
INSERT INTO `edu_score` VALUES ('1002', 'C005', '78');
INSERT INTO `edu_score` VALUES ('1003', 'C001', '69');
INSERT INTO `edu_score` VALUES ('1003', 'C002', '89');
INSERT INTO `edu_score` VALUES ('1003', 'C003', '85');
INSERT INTO `edu_score` VALUES ('1003', 'C004', '90');
INSERT INTO `edu_score` VALUES ('1003', 'C005', '79');
INSERT INTO `edu_score` VALUES ('1004', 'C001', '70');
INSERT INTO `edu_score` VALUES ('1004', 'C002', '90');
INSERT INTO `edu_score` VALUES ('1004', 'C003', '86');
INSERT INTO `edu_score` VALUES ('1004', 'C004', '91');
INSERT INTO `edu_score` VALUES ('1005', 'C001', '71');
INSERT INTO `edu_score` VALUES ('1005', 'C002', '91');
INSERT INTO `edu_score` VALUES ('1005', 'C003', '87');
INSERT INTO `edu_score` VALUES ('1005', 'C004', '92');
INSERT INTO `edu_score` VALUES ('1006', 'C001', '72');
INSERT INTO `edu_score` VALUES ('1006', 'C002', '92');
INSERT INTO `edu_score` VALUES ('1006', 'C003', '88');
INSERT INTO `edu_score` VALUES ('1006', 'C004', '93');

-- ----------------------------
-- Table structure for edu_student
-- ----------------------------
DROP TABLE IF EXISTS `edu_student`;
CREATE TABLE `edu_student` (
  `stu_id` varchar(16) NOT NULL COMMENT '学号',
  `stu_name` varchar(20) NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- ----------------------------
-- Records of edu_student
-- ----------------------------
INSERT INTO `edu_student` VALUES ('1001', '小明');
INSERT INTO `edu_student` VALUES ('1002', '小王');
INSERT INTO `edu_student` VALUES ('1003', '小李');
INSERT INTO `edu_student` VALUES ('1004', '小张');
INSERT INTO `edu_student` VALUES ('1005', '小阳');
INSERT INTO `edu_student` VALUES ('1006', '小胡');

使用case when 实现行转列的过滤

SELECT
	st.stu_id '学号',
	st.stu_name '姓名',
		CASE co.course_name
		WHEN '大学语文' THEN
			sc.scores
		ELSE
			0
		END '大学语文',
		CASE co.course_name
		WHEN '新视野英语' THEN
			sc.scores
		ELSE
			0
		END '新视野英语',
		CASE co.course_name
		WHEN '离散数学' THEN
			sc.scores
		ELSE
			0
		END '离散数学',
		CASE co.course_name
		WHEN '概率论与数理统计' THEN
			sc.scores
		ELSE
			0
		END '概率论与数理统计',
		CASE co.course_name
		WHEN '线性代数' THEN
			sc.scores
		ELSE
			0
		END '线性代数',
		CASE co.course_name
		WHEN '高等数学' THEN
			sc.scores
		ELSE
			0
		END '高等数学'
FROM
	edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no

查询结果：
结果是多行的，需要用分组函数在整合下，可以选用max和sum都可以

使用max整合结果

SELECT
	st.stu_id '学号',
	st.stu_name '姓名',
	max(
		CASE co.course_name
		WHEN '大学语文' THEN
			sc.scores
		ELSE
			0
		END
	) '大学语文',
	max(
		CASE co.course_name
		WHEN '新视野英语' THEN
			sc.scores
		ELSE
			0
		END
	) '新视野英语',
	max(
		CASE co.course_name
		WHEN '离散数学' THEN
			sc.scores
		ELSE
			0
		END
	) '离散数学',
	max(
		CASE co.course_name
		WHEN '概率论与数理统计' THEN
			sc.scores
		ELSE
			0
		END
	) '概率论与数理统计',
	max(
		CASE co.course_name
		WHEN '线性代数' THEN
			sc.scores
		ELSE
			0
		END
	) '线性代数',
	max(
		CASE co.course_name
		WHEN '高等数学' THEN
			sc.scores
		ELSE
			0
		END
	) '高等数学'
FROM
	edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
	st.stu_id
	
例外一种写法
SELECT 
	temp.学号 '学号',
	temp.姓名 '姓名',
	max(temp.大学语文) '大学语文',
	max(temp.新视野英语) '新视野英语',
	max(temp.离散数学) '离散数学',
	max(temp.概率论与数理统计) '概率论与数理统计',
	max(temp.线性代数) '线性代数',
	max(temp.高等数学) '高等数学'
from
	(SELECT
		st.stu_id '学号',
		st.stu_name '姓名',
		CASE co.course_name
		WHEN '大学语文' THEN
			sc.scores
		ELSE
			0
		END '大学语文',
		CASE co.course_name
		WHEN '新视野英语' THEN
			sc.scores
		ELSE
			0
		END '新视野英语',
		CASE co.course_name
		WHEN '离散数学' THEN
			sc.scores
		ELSE
			0
		END '离散数学',
		CASE co.course_name
		WHEN '概率论与数理统计' THEN
			sc.scores
		ELSE
			0
		END '概率论与数理统计',
		CASE co.course_name
		WHEN '线性代数' THEN
			sc.scores
		ELSE
			0
		END '线性代数',
		CASE co.course_name
		WHEN '高等数学' THEN
			sc.scores
		ELSE
			0
		END '高等数学'
	FROM
		edu_student st
	LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
	LEFT JOIN edu_courses co ON co.course_no = sc.course_no) temp
GROUP BY
	temp.姓名

查询结果：

推荐第一种写法：不会全表扫描

第二种写法会全表扫描