文档章节

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

HenrySun
 HenrySun
发布于 2016/07/12 10:51
字数 3242
阅读 57
收藏 4
点赞 1
评论 0

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

数据表结构

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

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

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

课程表

课程编号、课程名


CREATE TABLE `courses` (
  `courseno` VARCHAR(20) NOT NULL,
  `coursenm` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`courseno`)
)
COMMENT='课程表'
COLLATE='utf8_general_ci'
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='utf8_general_ci'
ENGINE=InnoDB;

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

数据准备

/*学生表数据*/
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, courseno, scores) Values('1002', 'C001', 68);
Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);
Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);
Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);
Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);
Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);
Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);
Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);
Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);
Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);
Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);
Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);
Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);
Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);
Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);
Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);
Insert Into score(stuid, courseno, scores) Values('1004', 'C005', 80);
Insert Into score(stuid, courseno, scores) Values('1005', 'C005', 81);
Insert Into score(stuid, courseno, scores) Values('1006', 'C005', 82);
Insert Into score(stuid, courseno, scores) Values('1001', 'C006', 77);
Insert Into score(stuid, courseno, scores) Values('1002', 'C006', 78);
Insert Into score(stuid, courseno, scores) Values('1003', 'C006', 79);
Insert Into score(stuid, courseno, scores) Values('1004', 'C006', 80);
Insert Into score(stuid, courseno, scores) Values('1005', 'C006', 81);
Insert Into score(stuid, courseno, scores) Values('1006', 'C006', 82);


为什么要行转列

 

 

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

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

怎么行转列

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

静态行转列

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


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

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

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

动态行转列

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

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

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 ) '离散数学'

而不是像上面那样一句句写出来,那如何得到这样的语句呢?
这里就要用到SQL语句拼接了。具体就是下面的语句

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


得到的结果就是

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


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

动态的列是拿到了,那如何再结合SQL语句进行查询得到结果呢?

这里要说明一点,因为用到了拼接函数,如果像上面的查询语句,只是把那几行语句替换掉,也就是下面这样

Select st.stuid, st.stunm, 
(
  SELECT
   GROUP_CONCAT(DISTINCT
    CONCAT(
     'MAX(IF(c.coursenm = ''',
     c.coursenm,
     ''', s.scores, 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;

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

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

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

SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',
   c.coursenm,
   ''', s.scores, 0)) AS ''',
   c.coursenm, ''''
  )
 ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @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;

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

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

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

语句则如下

SET @sql = NULL;
SET @stuid = '1003';
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',
   c.coursenm,
   ''', s.scores, 0)) AS ''',
   c.coursenm, ''''
  )
 ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 
            ' 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;

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

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

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

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

SET @sql = NULL;
SET @stuid = '1003';
SET @courseno = 'C002';
 
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'MAX(IF(c.coursenm = ''',
   c.coursenm,
   ''', s.scores, 0)) AS ''',
   c.coursenm, ''''
  )
 ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 
            ' From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno');
             
IF @stuid is not null and @stuid != 0 then
SET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');
END IF;  
 
SET @sql = CONCAT(@sql, ' Group by st.stuid');
 
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(
   'MAX(IF(c.coursenm = ''',
   c.coursenm,
   ''', s.scores, 0)) AS ''',
   c.coursenm, '\''
  )
 ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 
            ' From Student st 
            Left Join score s On st.stuid = s.stuid
            Left Join courses c On c.courseno = s.courseno');
             
IF stuid is not null and stuid <> '' then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');
END IF;  
 
SET @sql = CONCAT(@sql, ' Group by st.stuid');
 
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, ' Where st.stuid = ''', @stuid, '''');
END IF

嗯,就是这么简单明了,如果条件满足,那么就怎么样,然后结束。
然后我们就可以传参数调用这个SP了 

CALL `SP_QueryData`('1001');

得到如下结果

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

CALL `SP_QueryData`('');

同样得到我们想要的结果

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

总结及问题

 

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

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

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

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

可是我这么写了之后并没有什么用,还是报错,找了不少之后才发现原来不是这么写的,然后改了过来。
改完之后我以为可以了,可是,发现依旧不行。然后我就在想是不是这里不能用if判断,因为不是一个function或者procedure,于是我就写创建procedure的语句。

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

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

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

本文转载自:http://www.jb51.net/article/78388.htm

共有 人打赏支持
HenrySun
粉丝 85
博文 121
码字总数 41919
作品 0
深圳
高级程序员
经典SQL问题: 行转列

学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据库grade里面数据如下图,假定每个人姓名都不一样,作为主键。本文以MySQL为基础,其他数据库会...

闪电
2015/09/19
25.7K
0
SQL 经典问题:列转行

情景分析: 如上文 经典SQL问题: 行转列所示情节,反过来。 数据库数据为: 处理后效果(id可以不要): 方法一: SELECT*FROM(SELECTname,'语文'ascourse, 语文asscoreFROMgrade2unionall...

闪电
2015/09/19
56
0
Easyui动态显示列的应用实例

1、前言 项目中一个考勤信息功能,考勤信息的数据表结构如下: 考勤表数据如下图: 客户要求实现的界面样式如下图: 说明:根据年月查询店铺员的考勤信息,每个月份的天数是不一样的,5月份3...

ytangdigl
2017/06/17
0
0
mysql数据库优化五步走

MySQL数据库是一种小型关系型数据库管理系统,MySQL数据库的优化是MySQL数据库操作过程中非常重要的工作,MySQL数据库的优化能够实现MySQL数据库操作的简便。 第一步: 1:磁盘寻道能力,以高速...

安安-Anan
2015/09/01
36
0
(转)学习MySQL优化原理,这一篇就够了!

原文:https://mp.weixin.qq.com/sbiz=MzI4NTA1MDEwNg==&mid=2650763421&idx=1&sn=2515421f09c150d31e8d1b8b59243bd5&chksm=f3f9c508c48e4c1ea64b00b25c226efa2b9e32910f83290bf383ce0d16ee0......

weixin_39152648
05/09
0
0
mySQL 触发器

㈠CREATE TRIGGER语法 CREATE TRIGGER triggername triggertime trigger_event ON tblname FOR EACH ROW triggerstmt 触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对...

idoz
2015/10/20
20
0
转一篇非常好的MySQL优化的文章

MySQL的优化 作者:晏子 (2001-05-14 12:20:05) 一、我们可以且应该优化什 么? 硬件 操作系统/软件库 SQL服务器(设置和查询) 应用编程接口(API) 应用程序 --------------------------------...

红薯
2010/01/12
1K
4
mysql的存储引擎

前言: 数据库中的存储引擎其实是对使用了该引擎的表进行某种设置,数据库中的表设定了什么存储引擎,那么该表在数据存储方式、数据更新方式、数据查询性能以及是否支持索引等方面就会有不同...

丑的想整容
01/13
0
0
MySQL explain详解

例子 id SQL执行的顺序的标识 select_type 查询中每个select子句的类型 (1) SIMPLE(简单SELECT,不使用UNION或子查询等) (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为P...

乾坤刀
07/10
0
0
SQL服务器模式

服务器可以以不同的模式来操作,并且可以为不同客户端应用不同模式。这样每个应用程序可以根据自己的需求来定制的操作模式。 模式定义MySQL应支持哪些SQL语法,以及应执行哪种数据验证检查。...

技术小胖子
2017/11/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

微信小程序Java登录流程(ssm实现具体功能和加解密隐私信息问题解决方案)

文章有不当之处,欢迎指正,如果喜欢微信阅读,你也可以关注我的微信公众号:好好学java,获取优质学习资源。 一、登录流程图 二、小程序客户端 doLogin:function(callback = () =>{}){let ...

公众号_好好学java
38分钟前
0
1
流利阅读笔记28-20180717待学习

“我不干了!” 英国脱欧大臣递交辞呈 雪梨 2018-07-17 1.今日导读 7 月 6 日,英国政府高官齐聚英国首相的官方乡间别墅——契克斯庄园,讨论起草了一份关于英国政府脱欧立场的白皮书。可是没...

aibinxiao
今天
7
0
OSChina 周二乱弹 —— 理解超算排名这个事,竟然超出了很多人的智商

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @-冰冰棒- :分享Ed Sheeran/Beyoncé的单曲《Perfect Duet (with Beyoncé)》 《Perfect Duet (with Beyoncé)》- Ed Sheeran/Beyoncé 手机...

小小编辑
今天
180
7
Android 获取各大音乐平台的真实下载地址

废话 电脑使用谷歌浏览器或者QQ浏览器的时候。。。。。。。说不清楚,还是看图吧 大概意思就是,只要网页上需要播放,只要能播放并且开始播放,这个过程就肯定会请求到相关的音乐资源,然后就...

她叫我小渝
今天
0
0
shell中的函数、shell中的数组、告警系统需求分析

shell中的函数 格式: 格式: function f_name() { command } 函数必须要放在最前面 示例1(用来打印参数) 示例2(用于定义加法) 示例3(用于显示IP) shell中的数组 shell中的数组1 定义数...

Zhouliang6
今天
2
0
用 Scikit-Learn 和 Pandas 学习线性回归

      对于想深入了解线性回归的童鞋,这里给出一个完整的例子,详细学完这个例子,对用scikit-learn来运行线性回归,评估模型不会有什么问题了。 1. 获取数据,定义问题     没有...

wangxuwei
今天
1
0
MAC安装MAVEN

一:下载maven压缩包(Zip或tar可选),解压压缩包 二:打开终端输入:vim ~/.bash_profile(如果找不到该文件新建一个:touch ./bash_profile) 三:输入i 四:输入maven环境变量配置 MAVEN_HO...

WALK_MAN
今天
0
0
33.iptables备份与恢复 firewalld的9个zone以及操作 service的操作

10.19 iptables规则备份和恢复 10.20 firewalld的9个zone 10.21 firewalld关于zone的操作 10.22 firewalld关于service的操作 10.19 iptables规则备份和恢复: ~1. 保存和备份iptables规则 ~2...

王鑫linux
今天
2
0
大数据教程(2.11):keeperalived+nginx高可用集群搭建教程

上一章节博主为大家介绍了目前大型互联网项目的系统架构体系,相信大家应该注意到其中很重要的一块知识nginx技术,在本节博主将为大家分享nginx的相关技术以及配置过程。 一、nginx相关概念 ...

em_aaron
今天
1
1
Apache Directory Studio连接Weblogic内置LDAP

OBIEE默认使用Weblogic内置LDAP管理用户及组。 要整理已存在的用户及组,此前办法是导出安全数据,文本编辑器打开认证文件,使用正则表达式获取用户及组的信息。 后来想到直接用Apache Dire...

wffger
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部