作者:毕涛涛,致力于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
FROM
student A
INNER JOIN score C ON A.stuid = C.stuid
INNER JOIN courses B ON C.courseno = B.courseno;
成绩单1.png
2、行转列后的结果
上面是进行成绩查询的时候看到的纵列的结果,但是我们想要的是下面的结果,那么需要这样的结果就要进行 行转列 来操作了。
成绩单2.png三、实现过程
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 语句结合使用,主要目的是为了消除阶梯状数据。(关联学生表,分数表后,你的结果集会和分数表条数一致)
阶梯状数据.png
但我们都知道,课程不仅仅这几门,如果用上面的语句去写,第一要确定有多少课程,这么多课程的课程名要再拿出来,那样的话写一个查询语句下来,可是要写很多了。那么就想能不能动态进行行转列的操作?答案当然是肯定的了!
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拼接.png
对,没错,就是我们上面进行 行转列 查询要用的语句,那样就不用知道多少课程和这些课程的名字,只要这样几行代码便可以得到动态的列了。
动态的列是拿到了,那如何再结合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;
然而事与愿违,得到的结果却是这样的
事与愿违的结果.png
这里我就不多做赘述了,想必大家也明白。那么既然这样不行,那该怎么做呢?
没错,这里就要像普通的那些语句那样,进行声明,将语句拼接完整之后,再执行,也就是下面这样:
SET @sql = 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
GROUP BY st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
直接执行这些语句,得到如下结果:
期望的结果.png
当然这个语句拼接中的查询可以加入条件查询,比如我们要查询学号是1003的成绩
也就是下面这样:
1003学生成绩.png
语句则如下:
SET @sql = NULL;
SET @stuid = '1003';
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
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;
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');
同样得到我们想要的结果:
期望的结果.png
好了,以上就是这次我在mysql进行动态行转列的实现过程。
网友评论