前言

前言

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;

课程表 课程编号、课程名

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);

为什么要行转列

图片 1

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

图片 2

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

怎么行转列

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

静态行转列

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;

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

图片 3

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

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

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;

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

图片 4

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

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

图片 5

语句则如下

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');

得到如下结果

图片 6

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

CALL `SP_QueryData`('');

同样得到我们想要的结果

图片 7

好了,以上就是这次我在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语句中的使用不一样的地方等。
而且,这个行转列的实现了之后,这个项目基本上没啥大问题了对数据的处理,相当好啊,哈哈~

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

这是总结SQL知识点的第二篇文章,一次只总结一个知识点,尽量说明白。上次我们谈到行转列,用的是Pivot函数,这次我们来谈谈Unpivot函数。(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好)

未来的一个月时间中,会总结一系列SQL知识点,一次只总结一个知识点,尽量说明白,下面来说说SQL
中常用Pivot
函数(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好)

您可能感兴趣的文章:

  • mysql 存储过程中变量的定义与赋值操作
  • MySQL存储过程例子(包含事务,输出参数,嵌套调用)
  • MySQL动态创建表,数据分表的存储过程
  • mysql存储过程详解
  • mysql存储过程 游标 循环使用介绍
  • mysql 导入导出数据库以及函数、存储过程的介绍
  • MySQL存储过程使用实例详解
  • MySQL存储过程中游标循环的跳出和继续操作示例
  • MySQL 存储过程中执行动态SQL语句的方法
  • Node.js中调用mysql存储过程示例

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

先看一个小问题CustomerCustomer

让我们先从一个虚构的场景中来着手吧

在这张图中,表示的是顾客用不同手机号给Phone1、Phone2、Phone3拨打电话的情况,但是机灵的你,想变个花样来看看,比如下面这样的。

万国来朝,很多供应商每天都汇报各自的收入情况。先来创建一个DailyIncome 表

UnpivotUnpivot

create table DailyIncome, IncomeDay nvarchar, IncomeAmount int)--VendorId 供应商ID,--IncomeDay 收入时间--IncomeAmount 收入金额

大家想想看如何实现呢?想下,2分钟后再看哟

(留意看下,有的供应商某天中会有多次收入,应该是分批进账的)

 CREATE TABLE dbo.CustomerPhones( CustomerID INT PRIMARY KEY, -- FK Phone1 VARCHAR, Phone2 VARCHAR, Phone3 VARCHAR;INSERT dbo.CustomerPhones (CustomerID, Phone1, Phone2, Phone3)VALUES (1,'705-491-1111', '705-491-1110', NULL), (2,'613-492-2222', NULL, NULL), (3,'416-493-3333', '416-493-3330', '416-493-3339'); select * from dbo.CustomerPhones
insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values insert into DailyIncome values 

DataDataUpivot 实现行转列

让我们先来看看前十行数据:

 select * from dbo.CustomerPhones -----数据源 unpivot ( Phone FOR Phones IN (Phone1, Phone2, Phone3) ##Phone1、Phone2、Phone3这些列的结果转为行上的结果,成为一列, ##并且有了新的列名为Phone) up
select top 10 * from DailyIncome

ResultResult

DailyIncome

参考文献:

虽然数据是能够完全给展示了,但好像一眼望去不能得到对我们用处更大的信息,比如说我们想得到每个供应商的每天的总收入,这时我们应该做一些数据形式的转变了,平常的所用的是这样的。

1.Use SQL Server’s UNPIVOT operator to help normalize output

select VendorId ,sum(case when IncomeDay='MoN' then IncomeAmount else 0 end) MON,sum(case when IncomeDay='TUE' then IncomeAmount else 0 end) TUE,sum(case when IncomeDay='WED' then IncomeAmount else 0 end) WED,sum(case when IncomeDay='THU' then IncomeAmount else 0 end) THU,sum(case when IncomeDay='FRI' then IncomeAmount else 0 end) FRI,sum(case when IncomeDay='SAT' then IncomeAmount else 0 end) SAT,sum(case when IncomeDay='SUN' then IncomeAmount else 0 end) SUNfrom DailyIncome group by VendorId

2.SQL之行转列Pivot用法

case when结果

总结

如果大家仔细看结果的话,会有这样的发现,这是把VendorID进行了分组,并且对于每组中IncomeDay这一列中的值都变成了新的列名字,然后对IncomeAmount进行求和操作。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

这样写可能是有些麻烦,别着急,我们用Pivot函数进行行转列试下。

select * from DailyIncome ----第一步pivot  ----第三步for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步) as AvgIncomePerDay

来解释下,要想用好Pivot函数,应该理解代码注释中的这几步。

第一步:肯定是要明白数据源了,这里是DailyIncome

第二步:要明白要想让哪一列的值做新的列名字

第三步:要明白对于这新的列要求那些值呢?

下面有个练习题目,做之前不要看答案啊

问:对于SPIKE这家供应商来说,每天最大的入账金额。

select * from DailyIncomepivot  for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDaywhere VendorId in 

参考链接如下:

1.Pivot tables in SQL Server. A simple sample

2.行转列:SQL SERVER PIVOT与用法解释

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

相关文章