MySql行转列功能实现

---

前言

行专列功能在报表类的业务中还是比较常见的，下面我们通过常用的考试成绩表来实现这个功能

---

一、初始化脚本

1.学生表

CREATE TABLE `student`  (
  `stuid` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `stunm` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stuid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES ('s001', '小张');
INSERT INTO `student` VALUES ('s002', '小李');

2.课程表

CREATE TABLE `courses`  (
  `courseno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `coursenm` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `teacherNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`courseno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

INSERT INTO `courses` VALUES ('c001', '编译原理', 't001');
INSERT INTO `courses` VALUES ('c002', '操作系统', 't002');
INSERT INTO `courses` VALUES ('c003', '汇编语言', 't003');
INSERT INTO `courses` VALUES ('c004', '数据结构', 't004');
INSERT INTO `courses` VALUES ('c005', '计算机组成原理', 't005');

3.成绩表

CREATE TABLE `score`  (
  `stuid` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `courseno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `scores` float NULL DEFAULT NULL,
  PRIMARY KEY (`stuid`, `courseno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `score` VALUES ('s001', 'c001', 82);
INSERT INTO `score` VALUES ('s001', 'c002', 72);
INSERT INTO `score` VALUES ('s001', 'c003', 43);

---

二、实现过程

SELECT
	s.`stunm`,
	c.`coursenm`,
	sc.`scores` 
FROM
	student s
	LEFT JOIN score sc ON s.`stuid` = sc.stuid
	LEFT JOIN courses c ON sc.`courseno` = c.`courseno`

未做转换前 ，数据库里面的数据是这样的，在报表统计功能里面，我们需要把单个学生的所有科目考试成绩展示在同一行

也就是要实现这样的效果

很显然要达到这样的目的需要用学号来分组,下面给出两种实现方式

1、写死查询表头

SELECT
	stunm,
	MAX( CASE WHEN coursenm = '编译原理' THEN scores ELSE 0 END ) AS '编译原理',
	MAX( CASE WHEN coursenm = '操作系统' THEN scores ELSE 0 END ) AS '操作系统',
	MAX( CASE WHEN coursenm = '汇编语言' THEN scores ELSE 0 END ) AS '汇编语言',
	MAX( CASE WHEN coursenm = '数据结构' THEN scores ELSE 0 END ) AS '数据结构',
	MAX( CASE WHEN coursenm = '计算机组成原理' THEN scores ELSE 0 END ) AS '计算机组成原理' 
FROM
	(
	SELECT
		s.`stunm`,
		c.`coursenm`,
		sc.`scores` 
	FROM
		student s
		LEFT JOIN score sc ON s.`stuid` = sc.stuid
		LEFT JOIN courses c ON sc.`courseno` = c.`courseno` 
	) a 
GROUP BY
	stunm

通过max 函数跟 case when语句添加了表头

2、动态查询表头

前面的实现是针对列的情况可知(我们预先知道有多少们课程),但实际开发中肯定不能这样来处理，因为列数据有可能是会动态添加的，
我们构造表头要考虑到这个情况 (也就是前面说的max case when语句要动态拼接出来)，GROUP_CONCAT 这个函数可以帮助我们实现这个功能

	
SELECT
	GROUP_CONCAT( 'max(case when coursenm = trim(\'', coursenm, '\') then scores else 0 end ) as \'', coursenm, '\'' ) 
FROM
	courses

这个sql就实现了动态拼接表头的功能

我们用存储过程来实现完整的功能

DELIMITER $$
DROP PROCEDURE IF EXISTS `line_to_column`$$

CREATE  PROCEDURE `line_to_column`(IN stuid VARCHAR(10))
BEGIN
  SET @querySql = NULL ;
  SET @stuid = NULL ;
  -- 拼接查询标头
  SELECT 
    GROUP_CONCAT(
      'max(case when coursenm = trim(\'',-- 去空格 \用来转义
      coursenm,
      '\') then scores else 0 end ) as \'',
      coursenm,
      '\''
    ) INTO @querySql 
  FROM courses; 
  SET @querySql = CONCAT('select stunm,',@querySql,
      ' FROM (SELECT 
  s.stunm,
  c.coursenm,
  sc.scores
FROM
  student s 
  LEFT JOIN score sc 
    ON s.stuid = sc.stuid 
  LEFT JOIN courses c
    ON sc.courseno = c.courseno '
    ) ;
    
  -- 传入查询条件是否包含学号 不包含则全部查询
  IF stuid IS NOT NULL 
  AND stuid != '' 
  THEN SET @stuid = stuid ;
  SET @querySql = CONCAT( @querySql, ' where s.stuid = trim( \'', @stuid, '\' )'
  ) ;
  END IF ;
  SET @querySql = CONCAT(@querySql, ') a  GROUP BY stunm') ;
  PREPARE stmt FROM @querySql ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;
  
END$$

DELIMITER ;

至此 ，整个行转列的功能就完成了