学生成绩单出炉啦

 

材料：

（1）数据库，mysql

（2）场景为打印学生成绩单

1、创建数据表，学生表、课程表和分数表。
学生成绩单出炉啦
CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `psw` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

CREATE TABLE `t_course` (
  `CourseName` varchar(50) DEFAULT NULL,
  `CourseID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、创建存储过程，实现行列转换

基本原理，根据课程多少来生成行转列的SQL语句。

CREATE TABLE `t_score` (
  `stuID` int(11) DEFAULT NULL,
  `CourseID` int(11) DEFAULT NULL,
  `Score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
USE `test`$$

DROP PROCEDURE IF EXISTS `sp_score`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_score`()
BEGIN
 DECLARE v_sql TEXT;
 DECLARE ssql TEXT;
 DECLARE L_sql TEXT;
 DECLARE Add_sql TEXT;
 DECLARE R_sql TEXT;
 DECLARE CourseID INT;
 DECLARE CourseName VARCHAR(50);
 DECLARE no_more_Courses INT  DEFAULT 0;
 -- 定义游标
 DECLARE courses CURSOR
 FOR
            SELECT a.CourseID,a.CourseName FROM t_course a ;
 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_courses=1;
 SET no_more_courses=0;
 SET R_sql=' FROM student a,t_score b WHERE a.id=b.stuID GROUP BY a.id';
 SET L_sql=' SELECT a.id as ''学生编号'',a.name ''姓名''';
 -- 打开游标
 OPEN courses;
 -- 循环所有的行
 REPEAT
-- Get course
       FETCH courses INTO CourseID,CourseName;
        IF no_more_Courses=0 THEN
                 SET Add_sql = CONCAT(',MAX(CASE b.CourseID WHEN ',CourseID,' THEN b.Score ELSE 0 END) AS ''',CourseName,'''');
          SET L_sql = CONCAT(L_sql,ADD_sql);
        END IF; 
        UNTIL no_more_Courses
       END REPEAT; -- 循环结束
 -- 关闭游标
 CLOSE courses;
 SET v_sql=CONCAT(L_sql,R_sql);
 SET @ssql = v_sql;
 PREPARE sl FROM @ssql;
 EXECUTE sl;
 
    END$$

3、成绩单炒制成功啦

---调用存储过程

 CALL sp_score()