Mysql应用MySQL存储过程中使用动态行转列
《Mysql应用MySQL存储过程中使用动态行转列》要点: MYSQL实例本文介绍的实例成功的实现了动态行转列.下面我以一个简单的数据库为例子,说明一下. MYSQL实例数据表结构 MYSQL实例这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩 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; MYSQL实例课程表 MYSQL实例 CREATE TABLE `courses` ( `courseno` VARCHAR(20) NOT NULL,`coursenm` VARCHAR(100) NOT NULL,PRIMARY KEY (`courseno`) ) COMMENT='课程表' COLLATE='utf8_general_ci' ENGINE=InnoDB; MYSQL实例成绩表 MYSQL实例 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; MYSQL实例以上就是数据库表的结构了,这里没有建立外键,但是根据表的结构,可以清楚的看到成绩表中的学号和课程号是与学生表、课程表分别关联起来的. MYSQL实例数据准备 MYSQL实例 /*学生表数据*/ 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,scores) Values('1002',68); Insert Into score(stuid,scores) Values('1003',69); Insert Into score(stuid,scores) Values('1004',70); Insert Into score(stuid,scores) Values('1005',71); Insert Into score(stuid,scores) Values('1006',72); Insert Into score(stuid,'C002',87); Insert Into score(stuid,88); Insert Into score(stuid,89); Insert Into score(stuid,90); Insert Into score(stuid,91); Insert Into score(stuid,92); Insert Into score(stuid,'C003',83); Insert Into score(stuid,84); Insert Into score(stuid,85); Insert Into score(stuid,86); Insert Into score(stuid,'C004',93); Insert Into score(stuid,'C005',77); Insert Into score(stuid,78); Insert Into score(stuid,79); Insert Into score(stuid,80); Insert Into score(stuid,81); Insert Into score(stuid,82); Insert Into score(stuid,'C006',82); MYSQL实例为什么要行转列 MYSQL实例 MYSQL实例这是我们进行成绩查询的时候看到的这种纵列的结果,但是一般的时候,我们想要看到下图这种结果 MYSQL实例 MYSQL实例那么需要这样的结果就要进行行转列来操作了. MYSQL实例怎么行转列 MYSQL实例像得到上图的结果,一般的行转列,我们只需要这么做 MYSQL实例静态行转列 MYSQL实例 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 MYSQL实例看上面的语句可以看出,我们是在知道固定的几门课程之后,可以使用 MYSQL实例 MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores, MYSQL实例这样的语句来实现行转列 MYSQL实例但我们都知道,课程不仅仅这几门,如果用上面的语句去写,第一要确定有多少课程,这么多课程的课程名要再拿出来,那样的话写一个查询语句下来,可是要写很多了.那么就想能不能动态进行行转列的操作?答案当然是肯定的了! MYSQL实例动态行转列 MYSQL实例那么如何进行动态行转列呢? MYSQL实例首先我们要动态获取这样的语句 MYSQL实例 MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文',0) ELSE 0 END ) '离散数学' MYSQL实例而不是像上面那样一句句写出来,那如何得到这样的语句呢? MYSQL实例这里就要用到SQL语句拼接了.具体就是下面的语句 MYSQL实例 SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(c.coursenm = ''',c.coursenm,''',s.scores,0)) AS ''','''' ) ) FROM courses c; MYSQL实例得到的结果就是 MYSQL实例 MAX(IF(c.coursenm = '大学语文',0)) AS '大学语文',MAX(IF(c.coursenm = '新视野英语',0)) AS '新视野英语',MAX(IF(c.coursenm = '离散数学',0)) AS '离散数学',MAX(IF(c.coursenm = '概率论与数理统计',0)) AS '概率论与数理统计',MAX(IF(c.coursenm = '线性代数',0)) AS '线性代数',MAX(IF(c.coursenm = '高等数学(一)',0)) AS '高等数学(一)',MAX(IF(c.coursenm = '高等数学(二)',0)) AS '高等数学(二)' MYSQL实例对,没错,就是我们上面进行行转列查询要用的语句,那样就不用知道多少课程和这些课程的名字,只要这样几行代码便可以得到动态的列了. MYSQL实例动态的列是拿到了,那如何再结合SQL语句进行查询得到结果呢? MYSQL实例 Select st.stuid,( SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(c.coursenm = ''',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; MYSQL实例然而得到的结果却是这样的 MYSQL实例 MYSQL实例这里我就不多做赘述了,想必大家也明白.那么既然这样不行,那该怎么做呢? MYSQL实例没错,这里就要像普通的那些语句那样,进行声明,将语句拼接完整之后,再执行,也就是下面这样 MYSQL实例 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(c.coursenm = ''','''' ) ) INTO @sql FROM courses c; SET @sql = CONCAT('Select st.stuid,',@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; MYSQL实例直接执行这些语句,得到如下结果. MYSQL实例 MYSQL实例没错,和开始的时候那种全部拼出来的语句一样,这样就实现了动态行转列的目的了.而且我们不用知道多少课程,也无需把这些课程名一一列出来. MYSQL实例当然这个语句拼接中的查询可以加入条件查询,比如我们要查询学号是1003的成绩 MYSQL实例 MYSQL实例语句则如下 MYSQL实例 SET @sql = NULL; SET @stuid = '1003'; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(c.coursenm = ''',' 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; MYSQL实例对比前面的语句,我们可以看到在第二行的Left join后面我改了一些,还有就是前面的变量加了一个@stuid [ 注:这里的 @ 符号是在SQL语句定义变量习惯用法,我个人理解应该是用来区分吧!] MYSQL实例那么问题来了,行转列的查询已经实现了,怎么标题中还写着存储过程?对,就是存储过程! (编辑:安卓应用网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |