首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

作者：倒退淂磁带_628 | 来源：互联网 | 2017-05-12 16:11
这篇文章主要为大家介绍了MySQL存储过程中使用动态行转列的相关资料,需要的朋友可以参考下
本文介绍的实例成功的实现了动态行转列。下面我以一个简单的数据库为例子，说明一下。

数据表结构

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

学生表
就简单一点，学生学号、学生姓名两个字段

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

课程表
课程编号、课程名

CREATE TABLE `courses` (
  `courseno` VARCHAR(20) NOT NULL,
  `coursenm` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`courseno`)
)
COMMENT=&＃39;课程表&＃39;
COLLATE=&＃39;utf8_general_ci&＃39;
ENGINE=InnoDB;

成绩表
学生学号、课程号、成绩

CREATE TABLE `score` (
  `stuid` VARCHAR(16) NOT NULL,
  `courseno` VARCHAR(20) NOT NULL,
  `scores` FLOAT NULL DEFAULT NULL,
  PRIMARY KEY (`stuid`, `courseno`)
)
COLLATE=&＃39;utf8_general_ci&＃39;
ENGINE=InnoDB;

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

数据准备

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

为什么要行转列

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

那么需要这样的结果就要进行行转列来操作了。

怎么行转列

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

静态行转列

Select st.stuid, st.stunm, 
  MAX(CASE c.coursenm WHEN &＃39;大学语文&＃39; THEN s.scores ELSE 0 END ) &＃39;大学语文&＃39;,
  MAX(CASE c.coursenm WHEN &＃39;新视野英语&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;新视野英语&＃39;, 
  MAX(CASE c.coursenm WHEN &＃39;离散数学&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;离散数学&＃39;,
  MAX(CASE c.coursenm WHEN &＃39;概率论与数理统计&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;概率论与数理统计&＃39;,
  MAX(CASE c.coursenm WHEN &＃39;线性代数&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;线性代数&＃39;,
  MAX(CASE c.coursenm WHEN &＃39;高等数学(一)&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;高等数学(一)&＃39;,
  MAX(CASE c.coursenm WHEN &＃39;高等数学(二)&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;高等数学(二)&＃39;
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

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

MAX(CASE c.coursenm WHEN &＃39;线性代数&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;线性代数&＃39;,

这样的语句来实现行转列

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

动态行转列

那么如何进行动态行转列呢？

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

MAX(CASE c.coursenm WHEN &＃39;大学语文&＃39; THEN s.scores ELSE 0 END ) &＃39;大学语文&＃39;,
MAX(CASE c.coursenm WHEN &＃39;线性代数&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;线性代数&＃39;, 
MAX(CASE c.coursenm WHEN &＃39;离散数学&＃39; THEN ifnull(s.scores,0) ELSE 0 END ) &＃39;离散数学&＃39;

而不是像上面那样一句句写出来，那如何得到这样的语句呢？

这里就要用到SQL语句拼接了。具体就是下面的语句

SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
   c.coursenm,
   &＃39;&＃39;&＃39;, s.scores, 0)) AS &＃39;&＃39;&＃39;,
   c.coursenm, &＃39;&＃39;&＃39;&＃39;
  )
 )
FROM courses c;

得到的结果就是

MAX(IF(c.coursenm = &＃39;大学语文&＃39;, s.scores, 0)) AS &＃39;大学语文&＃39;,
MAX(IF(c.coursenm = &＃39;新视野英语&＃39;, s.scores, 0)) AS &＃39;新视野英语&＃39;,
MAX(IF(c.coursenm = &＃39;离散数学&＃39;, s.scores, 0)) AS &＃39;离散数学&＃39;,
MAX(IF(c.coursenm = &＃39;概率论与数理统计&＃39;, s.scores, 0)) AS &＃39;概率论与数理统计&＃39;,
MAX(IF(c.coursenm = &＃39;线性代数&＃39;, s.scores, 0)) AS &＃39;线性代数&＃39;,
MAX(IF(c.coursenm = &＃39;高等数学(一)&＃39;, s.scores, 0)) AS &＃39;高等数学(一)&＃39;,
MAX(IF(c.coursenm = &＃39;高等数学(二)&＃39;, s.scores, 0)) AS &＃39;高等数学(二)&＃39;

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

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

Select st.stuid, st.stunm, 
(
  SELECT
   GROUP_CONCAT(DISTINCT
    CONCAT(
     &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
     c.coursenm,
     &＃39;&＃39;&＃39;, s.scores, NULL)) AS &＃39;,
     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;

然而得到的结果却是这样的

这里我就不多做赘述了，想必大家也明白。那么既然这样不行，那该怎么做呢？

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

SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
   c.coursenm,
   &＃39;&＃39;&＃39;, s.scores, 0)) AS &＃39;&＃39;&＃39;,
   c.coursenm, &＃39;&＃39;&＃39;&＃39;
  )
 ) INTO @sql
FROM courses c;

SET @sql = CONCAT(&＃39;Select st.stuid, st.stunm, &＃39;, @sql, 
            &＃39; 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&＃39;);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

直接执行这些语句，得到如下结果。

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

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

语句则如下

SET @sql = NULL;
SET @stuid = &＃39;1003&＃39;;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
   c.coursenm,
   &＃39;&＃39;&＃39;, s.scores, 0)) AS &＃39;&＃39;&＃39;,
   c.coursenm, &＃39;&＃39;&＃39;&＃39;
  )
 ) INTO @sql
FROM courses c;

SET @sql = CONCAT(&＃39;Select st.stuid, st.stunm, &＃39;, @sql, 
            &＃39; From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno
            Where st.stuid = &＃39;&＃39;&＃39;, @stuid, &＃39;&＃39;&＃39;
            Group by st.stuid&＃39;);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

那么问题来了，行转列的查询已经实现了，怎么标题中还写着存储过程？对，没错，就是存储过程！

像上面的语句，我们如果直接在MySQL中操作是没问题的，但如果用到项目中，那么这个语句显然我们没法用，而且我这次做的项目是结合使用MyBatis，大家都知道在MyBatis中的XML文件中可以自己写SQL语句，但是这样的很显然我们没法放到XML文件中。

而且最关键的是，这里不能用 If 条件，好比我们要判断学号是否为空或者等于0再加上条件进行查询，可是这里不支持。
没错就是下面这样

SET @sql = NULL;
SET @stuid = &＃39;1003&＃39;;
SET @courseno = &＃39;C002&＃39;;

SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
   c.coursenm,
   &＃39;&＃39;&＃39;, s.scores, 0)) AS &＃39;&＃39;&＃39;,
   c.coursenm, &＃39;&＃39;&＃39;&＃39;
  )
 ) INTO @sql
FROM courses c;

SET @sql = CONCAT(&＃39;Select st.stuid, st.stunm, &＃39;, @sql, 
            &＃39; From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno&＃39;);
            
IF @stuid is not null and @stuid != 0 then
SET @sql = CONCAT(@sql, &＃39; Where st.stuid = &＃39;&＃39;&＃39;, @stuid, &＃39;&＃39;&＃39;&＃39;);
END IF;  

SET @sql = CONCAT(@sql, &＃39; Group by st.stuid&＃39;);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

对，我就是加上 if 之后人家就是不支持，就是这么任性。

所以就要用到存储过程啦，而且用存储过程的好处是，方便我们调用，相当于一个函数，其他可能也是类似的查询不需再重复写代码，直接调存储过程就好，还能随心所欲的加上if条件判断，多么美好的事情，哈哈~。

那么说到存储过程，这里该如何写呢？
创建存储过程的语句我就不多写了，这里呢把上面的查询语句直接放到创建存储过程的begin和end直接就可以了，如下：

DELIMITER && 
drop procedure if exists SP_QueryData;
Create Procedure SP_QueryData(IN stuid varchar(16))
READS SQL DATA 
BEGIN

SET @sql = NULL;
SET @stuid = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   &＃39;MAX(IF(c.coursenm = &＃39;&＃39;&＃39;,
   c.coursenm,
   &＃39;&＃39;&＃39;, s.scores, 0)) AS &＃39;&＃39;&＃39;,
   c.coursenm, &＃39;\&＃39;&＃39;
  )
 ) INTO @sql
FROM courses c;

SET @sql = CONCAT(&＃39;Select st.stuid, st.stunm, &＃39;, @sql, 
            &＃39; From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno&＃39;);
            
IF stuid is not null and stuid <> &＃39;&＃39; then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, &＃39; Where st.stuid = \&＃39;&＃39;, @stuid, &＃39;\&＃39;&＃39;);
END IF;  

SET @sql = CONCAT(@sql, &＃39; Group by st.stuid&＃39;);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END && 

DELIMITER ;

嗯，对比上面简单的SQL语句可以看出，这里使用了 if 语句，对学号进行了判断

不过这里要注意一点，这里的if语句不像我们平时java啊那种写法也就是下面

if(条件)
{
要执行的语句块
}
对，在SQL里面的if语句不一样，不需要括号啊什么的，就像直接说英文一样

IF @stuid is not null and @stuid != 0 then
SET @sql = CONCAT(@sql, &＃39; Where st.stuid = &＃39;&＃39;&＃39;, @stuid, &＃39;&＃39;&＃39;&＃39;);
END IF;

嗯，就是这么简单明了，如果条件满足，那么就怎么样，然后结束。

然后我们就可以传参数调用这个SP了

CALL `SP_QueryData`(&＃39;1001&＃39;);

得到如下结果


当然我们也可以直接传个空串过去

CALL `SP_QueryData`(&＃39;&＃39;);

同样得到我们想要的结果

好了，以上就是这次我在MySQL进行动态行转列的实现过程。

总结及问题

开始的时候，只想到要行转列，写着写着突然发现要动态的，因为我不确定到底有多少列。
在网上各种找资料，然而看不太懂！

后来，参考了Pivot table with dynamic columns in MySQL这个，才写出来的。

然后是各种问题，先是SQL语句中加入if条件，我像平时写java那样，发现并没有什么用，网上也说就是这种

IF(stuid is not null && stuid <> &＃39;&＃39;) then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, &＃39; Where st.stuid = \&＃39;&＃39;, @stuid, &＃39;\&＃39;&＃39;);
END IF;

可是我这么写了之后并没有什么用，还是报错，找了不少之后才发现原来不是这么写的，然后改了过来。

改完之后我以为可以了，可是，发现依旧不行。然后我就在想是不是这里不能用if判断，因为不是一个function或者procedure，于是我就写创建procedure的语句。

改造完之后，procedure成功的创建了。那创建完我就试试能不能，调用procedure之后，当当当当，结果出来了。

嗯，这个过程还是收获很多的，对MySQL的行转列，以及存储过程，还有在SQL语句中的使用不一样的地方等。
而且，这个行转列的实现了之后，这个项目基本上没啥大问题了对数据的处理，相当好啊，哈哈~

以上就是我在行转列实现的过程中所有的内容，相对来说，我觉得，这里写的很清楚很明了了，所以只要你有耐心看完并认真研究的话，这个内容对你的行转列还是有很大裨益的。

以上就是MySQL存储过程中使用动态行转列_MySQL的内容，更多相关内容请关注PHP中文网（www.php1.cn）！

推荐阅读
author-avatar
倒退淂磁带_628
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有