作者:毕涛涛,致力于Java学习的践行者。原创文章,转载请注明出处。
参考链接: https://blog.csdn.net/wqc19920906/article/details/79791322一、创建表结构、插入数据
1、三种表:学生表、课程表、分数表
-- 学生表 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`stuid`) ) COMMENT '学生表'; -- 课程表 CREATE TABLE `courses` ( `courseno` VARCHAR(20) NOT NULL, `coursenm` VARCHAR(100) NOT NULL, PRIMARY KEY (`courseno`) ) COMMENT '课程表'; -- 成绩表 CREATE TABLE `score` ( `stuid` VARCHAR(16) NOT NULL, `courseno` VARCHAR(20) NOT NULL, `scores` FLOAT NULL DEFAULT NULL, PRIMARY KEY (`stuid`, `courseno`) ) COMMENT '成绩表';
2、插入数据
/*学生表数据*/ 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 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、查询学生姓名、对应课程以及相应的分数
SELECT A.stuid, A.stunm, B.coursenm, C.scores student A INNER JOIN score C ON A.stuid = C.stuid INNER JOIN courses B ON C.courseno = B.courseno;
1、静态行转列
SELECT st.stuid, st.stunm, SUM(CASE c.coursenm WHEN '大学语文' THEN ifnull(s.scores,0) ELSE 0 END ) '大学语文', SUM(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语', SUM(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学', SUM(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计', SUM(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数', SUM(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;
看上面的语句可以看出,我们是在知道固定的几门课程之后,可以使用这样的语句来实现行转列。
SUM(CASE c.coursenm WHEN '大学语文' THEN ifnull(s.scores,0) ELSE 0 END ) '大学语文',
与后面的GROUP BY 语句结合使用,主要目的是为了消除阶梯状数据。(关联学生表,分数表后,你的结果集会和分数表条数一致)
但我们都知道,课程不仅仅这几门,如果用上面的语句去写,第一要确定有多少课程,这么多课程的课程名要再拿出来,那样的话写一个查询语句下来,可是要写很多了。那么就想能不能动态进行行转列的操作?答案当然是肯定的了!
2、动态行转列
那么如何进行动态行转列呢?首先我们要动态获取这样的语句
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 ) '离散数学',
而不是像上面那样一句句写出来,那如何得到这样的语句呢?这里就要用到SQL语句拼接了。具体就是下面的语句:
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, '''' FROM courses c; 得到的结果就是
对,没错,就是我们上面进行 行转列 查询要用的语句,那样就不用知道多少课程和这些课程的名字,只要这样几行代码便可以得到动态的列了。
动态的列是拿到了,那如何再结合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 = 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; 直接执行这些语句,得到如下结果: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( 'SUM(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( 'SUM(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; 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'); 同样得到我们想要的结果: