MySQL存储过程中使用动态行转列_MySQL
- PHP中文网Original
- 2016-05-27 13:45:301457browse
本文介绍的实例成功的实现了动态行转列。下面我以一个简单的数据库为例子，说明一下。
数据表结构
这里我用一个比较简单的例子来说明，也是行转列的经典例子，就是学生的成绩
三张表：学生表、课程表、成绩表
学生表
就简单一点，学生学号、学生姓名两个字段
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语句中的使用不一样的地方等。
而且，这个行转列的实现了之后，这个项目基本上没啥大问题了对数据的处理，相当好啊，哈哈~
以上就是我在行转列实现的过程中所有的内容，相对来说，我觉得，这里写的很清楚很明了了，所以只要你有耐心看完并认真研究的话，这个内容对你的行转列还是有很大裨益的。
以上就是MySQL存储过程中使用动态行转列_MySQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Google推出下一代托管MySQL产品_MySQLNext article：MySQL恢复中的几个问题解决方法_MySQL