-- 學生信息表
DROP TABLE IF EXISTS t_student;
CREATE TABLE `t_student` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `code` VARCHAR(10) NOT NULL COMMENT '學號',
  `name` VARCHAR(20) NOT NULL COMMENT '姓名',
  `age` INT(2) NOT NULL COMMENT '年齡',
  `gender` CHAR(1) NOT NULL COMMENT '性別（M：男，F(xiàn)：女）',
  PRIMARY KEY (`id`),
  UNIQUE KEY UK_STUDENT (`code`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 學生成績表
DROP TABLE IF EXISTS t_achievement;
CREATE TABLE `t_achievement` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `year` INT(4) NOT NULL COMMENT '學年',
  `subject` CHAR(2) NOT NULL COMMENT '科目（01：語文，02：數(shù)學，03：英語）',
  `score` INT(3) NOT NULL COMMENT '得分',
  `student_id` BIGINT(12) NOT NULL COMMENT '所屬學生id',
  PRIMARY KEY (`id`) 
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 成績匯總表
DROP TABLE IF EXISTS t_achievement_report;
CREATE TABLE `t_achievement_report` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `student_id` BIGINT(12) NOT NULL COMMENT '學生id',
  `year` INT(4) NOT NULL COMMENT '學年',
  `total_score` INT(4) NOT NULL COMMENT '總分',
  `avg_score` DECIMAL(4,2) NOT NULL COMMENT '平均分',
  PRIMARY KEY (`id`) 
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO t_student(id, CODE, NAME, age, gender) VALUES
(1, '2022010101', '小張', 18, 'M'),
(2, '2022010102', '小李', 18, 'F'),
(3, '2022010103', '小明', 18, 'M');
INSERT INTO t_achievement(YEAR, SUBJECT, score, student_id) VALUES
(2022, '01', 80, 1),
(2022, '02', 85, 1),
(2022, '03', 90, 1),
(2022, '01', 60, 2),
(2022, '02', 90, 2),
(2022, '03', 98, 2),
(2022, '01', 75, 3),
(2022, '02', 100, 3),
(2022, '03', 85, 3);

-- 如果存儲過程存在，先刪除存儲過程
DROP PROCEDURE IF EXISTS statistics_achievement;
DELIMITER $$
-- 定義存儲過程
CREATE PROCEDURE statistics_achievement()
BEGIN
        -- 定義變量記錄循環(huán)處理是否完成
	DECLARE done BOOLEAN DEFAULT FALSE;
        -- 定義變量傳遞學生id
	DECLARE studentid BIGINT(12);
	-- 定義游標
	DECLARE cursor_student CURSOR FOR SELECT id FROM t_student;
	-- 定義CONTINUE HANDLER，當循環(huán)結束時 done=true
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;
	-- 打開游標
	OPEN cursor_student;
	-- 重復遍歷
	REPEAT 
		-- 每次讀取一次游標
		FETCH cursor_student INTO studentid;
                -- 計算總分、平均分插入?yún)R總表
		INSERT INTO t_achievement_report(student_id, `YEAR`, total_score, avg_score)
		SELECT studentid, `YEAR`, SUM(score), ROUND(SUM(score) / 3, 2) FROM t_achievement t1 WHERE student_id = studentid AND NOT EXISTS(
			SELECT 1 FROM t_achievement_report t2 WHERE student_id = studentid AND t1.year = t2.year
		) GROUP BY `YEAR`;
	-- 結束循環(huán)，意思是等到done=true時，結束循環(huán)REPEAT
	UNTIL done END REPEAT;
	-- 查詢結果，僅會展示查出的最后一條
	SELECT studentid;
	-- 關閉游標
	CLOSE cursor_student;
END$$
DELIMITER ;

-- 執(zhí)行存儲過程
CALL statistics_achievement();