mysql动态行转列函数_MySQL存储过程当中使用动态行转列

最新推荐文章于 2024-07-15 15:51:35 发布

诗和远方越远越脏

最新推荐文章于 2024-07-15 15:51:35 发布

阅读量804

点赞数

文章标签： mysql动态行转列函数

本文链接：https://blog.csdn.net/weixin_33773996/article/details/113454775

版权

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

数据表结构sql

这里我用一个比较简单的例子来讲明，也是行转列的经典例子，就是学生的成绩

三张表：学生表、课程表、成绩表数据库

学生表

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

CREATE TABLE `student` (

`stuid` VARCHAR(16) NOT NULL COMMENT '学号',

`stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',

PRIMARY KEY (`stuid`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

课程表ui

课程编号、课程名spa

CREATE TABLE `courses` (

`courseno` VARCHAR(20) NOT NULL,

`coursenm` VARCHAR(100) NOT NULL,

PRIMARY KEY (`courseno`)

)

COMMENT='课程表'

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

成绩表code

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

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;

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

数据准备it

/*学生表数据*/

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

为何要行转列

6b85e1ee40214e11871bc7bc.html

这是咱们进行成绩查询的时候看到的这种纵列的结果，可是通常的时候，咱们想要看到下图这种结果

6b85e1ee40214e11871bc7bc.html

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

怎么行转列

像获得上图的结果，通常的行转列，咱们只须要这么作

静态行转列

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;

然而获得的结果倒是这样的

6b85e1ee40214e11871bc7bc.html

这里我就很少作赘述了，想必你们也明白。那么既然这样不行，那该怎么作呢？

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

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;

直接执行这些语句，获得以下结果。

6b85e1ee40214e11871bc7bc.html

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

固然这个语句拼接中的查询能够加入条件查询，好比咱们要查询学号是1003的成绩

也就是下面这样

6b85e1ee40214e11871bc7bc.html

语句则以下

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

获得以下结果

6b85e1ee40214e11871bc7bc.html

固然咱们也能够直接传个空串过去

CALL `SP_QueryData`('');

一样获得咱们想要的结果

6b85e1ee40214e11871bc7bc.html

好了，以上就是此次我在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语句中的使用不同的地方等。

并且，这个行转列的实现了以后，这个项目基本上没啥大问题了对数据的处理，至关好啊，哈哈~

以上就是我在行转列实现的过程当中全部的内容，相对来讲，我以为，这里写的很清楚很明了了，因此只要你有耐心看完并认真研究的话，这个内容对你的行转列仍是有很大裨益的。

诗和远方越远越脏

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql动态行转列函数_MySQL存储过程当中使用动态行转列

本文介绍的实例成功的实现了动态行转列。下面我以一个简单的数据库为例子，说明一下。java数据表结构sql这里我用一个比较简单的例子来讲明，也是行转列的经典例子，就是学生的成绩三张表：学生表、课程表、成绩表数据库学生表就简单一点，学生学号、学生姓名两个字段函数CREATE TABLE `student` (`stuid` VARCHAR(16) NOT NULL COMMENT '学号',`stun...
复制链接

扫一扫