Mysql应用MySQL存储过程中使用动态行转列

《Mysql应用MySQL存储过程中使用动态行转列》要点：
本文介绍了Mysql应用MySQL存储过程中使用动态行转列，希望对您有用。如果有疑问，可以联系我们。

MYSQL实例本文介绍的实例成功的实现了动态行转列.下面我以一个简单的数据库为例子,说明一下.

MYSQL实例数据表结构

MYSQL实例这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩
三张表：学生表、课程表、成绩表

MYSQL实例学生表
就简单一点,学生学号、学生姓名两个字段

MYSQL实例
CREATE TABLE `student` (
  `stuid` VARCHAR(16) NOT NULL COMMENT '学号',`stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',PRIMARY KEY (`stuid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

MYSQL实例课程表
课程编号、课程名

MYSQL实例
CREATE TABLE `courses` (
  `courseno` VARCHAR(20) NOT NULL,`coursenm` VARCHAR(100) NOT NULL,PRIMARY KEY (`courseno`)
)
COMMENT='课程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

MYSQL实例成绩表
学生学号、课程号、成绩

MYSQL实例
CREATE TABLE `score` (
  `stuid` VARCHAR(16) NOT NULL,`courseno` VARCHAR(20) NOT NULL,`scores` FLOAT NULL DEFAULT NULL,PRIMARY KEY (`stuid`,`courseno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

MYSQL实例以上就是数据库表的结构了,这里没有建立外键,但是根据表的结构,可以清楚的看到成绩表中的学号和课程号是与学生表、课程表分别关联起来的.

MYSQL实例数据准备

MYSQL实例
/*学生表数据*/
Insert Into student (stuid,stunm) Values('1001','张三');
Insert Into student (stuid,stunm) Values('1002','李四');
Insert Into student (stuid,stunm) Values('1003','赵二');
Insert Into student (stuid,stunm) Values('1004','王五');
Insert Into student (stuid,stunm) Values('1005','刘青');
Insert Into student (stuid,stunm) Values('1006','周明');
/*课程表数据*/
Insert Into courses (courseno,coursenm) Values('C001','大学语文');
Insert Into courses (courseno,coursenm) Values('C002','新视野英语');
Insert Into courses (courseno,coursenm) Values('C003','离散数学');
Insert Into courses (courseno,coursenm) Values('C004','概率论与数理统计');
Insert Into courses (courseno,coursenm) Values('C005','线性代数');
Insert Into courses (courseno,coursenm) Values('C006','高等数学(一)');
Insert Into courses (courseno,coursenm) Values('C007','高等数学(二)');
/*成绩表数据*/
Insert Into score(stuid,courseno,scores) Values('1001','C001',67);
Insert Into score(stuid,scores) Values('1002',68);
Insert Into score(stuid,scores) Values('1003',69);
Insert Into score(stuid,scores) Values('1004',70);
Insert Into score(stuid,scores) Values('1005',71);
Insert Into score(stuid,scores) Values('1006',72);
Insert Into score(stuid,'C002',87);
Insert Into score(stuid,88);
Insert Into score(stuid,89);
Insert Into score(stuid,90);
Insert Into score(stuid,91);
Insert Into score(stuid,92);
Insert Into score(stuid,'C003',83);
Insert Into score(stuid,84);
Insert Into score(stuid,85);
Insert Into score(stuid,86);
Insert Into score(stuid,'C004',93);
Insert Into score(stuid,'C005',77);
Insert Into score(stuid,78);
Insert Into score(stuid,79);
Insert Into score(stuid,80);
Insert Into score(stuid,81);
Insert Into score(stuid,82);
Insert Into score(stuid,'C006',82);

MYSQL实例为什么要行转列

MYSQL实例

MYSQL实例这是我们进行成绩查询的时候看到的这种纵列的结果,但是一般的时候,我们想要看到下图这种结果

MYSQL实例

MYSQL实例那么需要这样的结果就要进行行转列来操作了.

MYSQL实例怎么行转列

MYSQL实例像得到上图的结果,一般的行转列,我们只需要这么做

MYSQL实例静态行转列

MYSQL实例
Select st.stuid,st.stunm,MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',MAX(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语',MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',MAX(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计',MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',MAX(CASE c.coursenm WHEN '高等数学(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(一)',MAX(CASE c.coursenm WHEN '高等数学(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(二)'
From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid

MYSQL实例看上面的语句可以看出,我们是在知道固定的几门课程之后,可以使用

MYSQL实例
MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,

MYSQL实例这样的语句来实现行转列

MYSQL实例但我们都知道,课程不仅仅这几门,如果用上面的语句去写,第一要确定有多少课程,这么多课程的课程名要再拿出来,那样的话写一个查询语句下来,可是要写很多了.那么就想能不能动态进行行转列的操作?答案当然是肯定的了!

MYSQL实例动态行转列

MYSQL实例那么如何进行动态行转列呢?

MYSQL实例首先我们要动态获取这样的语句

MYSQL实例
MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',0) ELSE 0 END ) '离散数学'

MYSQL实例而不是像上面那样一句句写出来,那如何得到这样的语句呢?

MYSQL实例这里就要用到SQL语句拼接了.具体就是下面的语句

MYSQL实例
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',c.coursenm,''',s.scores,0)) AS ''',''''
  )
 )
FROM courses c;

MYSQL实例得到的结果就是

MYSQL实例
MAX(IF(c.coursenm = '大学语文',0)) AS '大学语文',MAX(IF(c.coursenm = '新视野英语',0)) AS '新视野英语',MAX(IF(c.coursenm = '离散数学',0)) AS '离散数学',MAX(IF(c.coursenm = '概率论与数理统计',0)) AS '概率论与数理统计',MAX(IF(c.coursenm = '线性代数',0)) AS '线性代数',MAX(IF(c.coursenm = '高等数学(一)',0)) AS '高等数学(一)',MAX(IF(c.coursenm = '高等数学(二)',0)) AS '高等数学(二)'

MYSQL实例对,没错,就是我们上面进行行转列查询要用的语句,那样就不用知道多少课程和这些课程的名字,只要这样几行代码便可以得到动态的列了.

MYSQL实例动态的列是拿到了,那如何再结合SQL语句进行查询得到结果呢?
这里要说明一点,因为用到了拼接函数,如果像上面的查询语句,只是把那几行语句替换掉,也就是下面这样

MYSQL实例
Select st.stuid,(
  SELECT
   GROUP_CONCAT(DISTINCT
    CONCAT(
     'MAX(IF(c.coursenm = ''',NULL)) AS ',c.coursenm
    )
   )
  FROM courses c
)
From Student st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid;

MYSQL实例然而得到的结果却是这样的

MYSQL实例

MYSQL实例这里我就不多做赘述了,想必大家也明白.那么既然这样不行,那该怎么做呢?

MYSQL实例没错,这里就要像普通的那些语句那样,进行声明,将语句拼接完整之后,再执行,也就是下面这样

MYSQL实例
SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',''''
  )
 ) INTO @sql
FROM courses c;
SET @sql = CONCAT('Select st.stuid,',@sql,' From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno
            Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MYSQL实例直接执行这些语句,得到如下结果.

MYSQL实例

MYSQL实例没错,和开始的时候那种全部拼出来的语句一样,这样就实现了动态行转列的目的了.而且我们不用知道多少课程,也无需把这些课程名一一列出来.

MYSQL实例当然这个语句拼接中的查询可以加入条件查询,比如我们要查询学号是1003的成绩
也就是下面这样

MYSQL实例

MYSQL实例语句则如下

MYSQL实例
SET @sql = NULL;
SET @stuid = '1003';
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',' From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno
            Where st.stuid = ''',@stuid,'''
            Group by st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MYSQL实例对比前面的语句,我们可以看到在第二行的Left join后面我改了一些,还有就是前面的变量加了一个@stuid [ 注：这里的 @ 符号是在SQL语句定义变量习惯用法,我个人理解应该是用来区分吧!]

MYSQL实例那么问题来了,行转列的查询已经实现了,怎么标题中还写着存储过程?对,就是存储过程!