mysql实现行转列功能-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

实现的功能

在实现报表类的业务功能时数据库存储的表结构可能需要转换（这里需要每一行记录中都保存单个学生的所有课程成绩）

本文用常见的学生表，成绩表来实现

初始的表结构

图一

需要实现的功能

图二

建表SQL

学生表

CREATE TABLE `student` (
  `stuid` varchar(16) NOT NULL COMMENT '学号',
  `stunm` varchar(20) NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

课程表

CREATE TABLE `courses` (
  `courseno` varchar(20) NOT NULL,
  `coursenm` varchar(100) NOT NULL,
  `teacherNo` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表'

成绩表

CREATE TABLE `score` (
  `stuid` varchar(16) NOT NULL,
  `courseno` varchar(20) NOT NULL,
  `scores` float DEFAULT NULL,
  PRIMARY KEY (`stuid`,`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

实现过程

1 我们需要将三表关联构造出一个含有学生名，课程名，分数的基表

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`

2 要实现在每一行中展现学生的所有成绩，应当根据构造出的表中的学生来分组

  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'线性代数' ,
           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

group by 语句在数据库表中根据group by 的字段进行分组

MAX(CASE WHEN coursenm ='大学语文' THEN scores ELSE 0 END) AS'大学语文'

这条语句的目的是得到某个学生大学语文的成绩(比如：张三，我们知道在聚合之前名字为张三的数据记录有7（总共七门课程）条，聚合时对这七条数据做处理如果课程名是大学语文则返回对应的成绩如果不是则设为0 这样max 配合case when 语句就得到了某个学生的语文成绩了)，其他课程类似，这样我们得到了所要的结果。

动态拼接查询表头

这里我们意识到我们是知道所有课程名才能这样处理，实际开发中这个条件我们是不知道的，也就是说要考虑动态的情况我们看到查询的标题数据格式差不多跟数据库中的课程名字段相关

好吧我么可以这样来实现，要用到一个函数

GROUP_CONCAT

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

这样就能实现表头的结构

存储过程实现

附上我的存储过程通用模板

USE `proceduretest`$$

DROP PROCEDURE IF EXISTS `paramtest`$$

CREATE  PROCEDURE `paramtest`(
 
IN inputid INT ,OUT outputid INT, INOUT iando INT
 
)
BEGIN 

END$$

DELIMITER ;

这里存储过程的实现只传入学生ID就可以了查询语句需要拼接

DELIMITER $$
DROP PROCEDURE IF EXISTS `linetorowhen`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linetorowhen`(IN stuid INT)
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 ;

转载于:https://my.oschina.net/u/1377006/blog/777641