原数据
想要的效果
先看下 下面的sql语句
主要是利用group by分组,将分组中的结果集作为参数去做查询
先写出静态sql
SELECT
NAME,
MAX(
CASE course
WHEN '语文' THEN
score
END
) AS '语文',
SUM(
CASE course
WHEN '数学' THEN
score
END
) AS '数学',
SUM(
CASE course
WHEN '英语' THEN
score
END
) AS '英语'
FROM
sshdb.grade2 g
GROUP BY
g.`name`
如果出现动态科目,利用存储过程
DELIMITER &&
CREATE PROCEDURE sp_count()
BEGIN
#课程名称
DECLARE course_n VARCHAR(20);
#所有课程数量
DECLARE count INT;
#计数器
DECLARE i INT DEFAULT 0;
#拼接SQL字符串
SET @s = 'SELECT name';
SET count = (SELECT COUNT(distinct course) FROM grade2);
WHILE i < count DO
SET course_n = (SELECT course FROM grade2 LIMIT i,1);
SET @s = CONCAT(@s, ', SUM(CASE course WHEN ','\'', course_n,'\'',' THEN score END )',' AS ','\'',course_n,'\'');
SET i = i+1;
END WHILE;
SET @s = CONCAT(@s, ' FROM grade2 GROUP BY name');
#用于调试
#SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
END
&&
call sp_count();