本文介绍的实例成功的实现了动态行转列。下面我以一个简单的数据库为例子,说明一下。
数据表结构
这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩
三张表:学生表、课程表、成绩表
学生表
就简单一点,学生学号、学生姓名两个字段
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存储过程中构建和使用动态SQL? 问题答案: 我不相信MySQL支持动态sql。您可以执行类似但不同的“准备”语句。 这是一个例子: 准备好的语句通常用于查看给定查询的执行计划。由于它们是使用 execute 命令执行的,并且可以将 sql 分配给变量,因此您可以近似地估计一些与动态sql相同的行为。 这是一个很好的链接: 不要忘记使用最后一行取消分配! 祝好运!
问题内容: 我在存储过程中创建一个动态查询。我的存储过程如下: 当我尝试通过以下调用运行它时: 我收到以下错误消息: 错误代码:1054。“ where子句”中的未知列“ SPA” 我在没有where条件的情况下进行了测试,并且工作正常,但是在where条件不起作用的情况下,我尝试使用@和变量名一起使用,但仍然无法正常工作。 谢谢你的帮助。 问题答案: 您错过了条款中的引号。 尝试这样: 说明 :
问题内容: 我想在sql查询中更改表的动态名称。例如,我有下一个存储过程: 我需要在运行时更改 tableName ,我可以这样做吗?谢谢。 问题答案: 您必须使用动态SQL来准备和执行SQL字符串,以实现您所描述的内容。 在准备之前,必须将动态表名称(或列名称或SQL关键字等)内插到SQL字符串中。您不能将查询参数用于这些动态元素。 当您将表名称插入到SQL查询中时,请小心避免SQL注入漏洞。例
本文向大家介绍MySQL 存储过程中执行动态SQL语句的方法,包括了MySQL 存储过程中执行动态SQL语句的方法的使用技巧和注意事项,需要的朋友参考一下
问题内容: 我在任何地方都找不到此答案,但是可以从MySQL中的另一个存储过程调用存储过程吗?我想找回标识值,并在父存储过程中使用它。我们不能再使用FUNCTIONS! 问题答案: 参数应该可以帮助您将值返回给调用过程。基于此,解决方案必须是这样的。
本文向大家介绍在MySQL存储过程中实现动态SQL查询?,包括了在MySQL存储过程中实现动态SQL查询?的使用技巧和注意事项,需要的朋友参考一下 对于存储过程中的动态SQL查询,请使用PREPARE STATEMENT的概念。让我们首先创建一个表- 使用插入命令在表中插入一些记录- 使用select语句显示表中的所有记录- 这将产生以下输出- 以下是创建存储过程并实现动态SQL的查询- 调用存储
1选定列的参数(选定的筛选器/下拉属性或从UI发送的列名) 2 dynamic where子句的参数,用于为点1中选择的类似下拉列表值准备 从视图中获取数据,并以这种方式检索结果 注意:在这一点上没有要求显示特定于某个用户的数据。同样忽略上面代码中的参数插值部分 是否存在任何数据完整性问题,不同的用户将看不到UI上更新的值(为了提供更多的上下文,从此过程中的视图中提取数据,并且该视图数据在应用程序
我们前面所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句,但是在数据库的实际操作中,经常会有需要多条 SQL 语句处理多个表才能完成的操作。 例如,为了确认学生能否毕业,需要同时查询学生档案表、成绩表和综合表,此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。 存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL