mysql写一个存储过程统计每年每门课程及格的学生人数和不及格的学生人数。
时间: 2023-07-18 11:46:27 浏览: 54
好的,以下是存储过程的代码:
```sql
DELIMITER $$
CREATE PROCEDURE `count_students`()
BEGIN
DECLARE year INT;
DECLARE course VARCHAR(50);
DECLARE pass_count INT;
DECLARE fail_count INT;
DECLARE cur CURSOR FOR SELECT DISTINCT year, course FROM grades;
OPEN cur;
SET pass_count = 0;
SET fail_count = 0;
read_loop: LOOP
FETCH cur INTO year, course;
IF (year IS NULL) THEN
LEAVE read_loop;
END IF;
SELECT COUNT(*) INTO pass_count FROM grades WHERE year = year AND course = course AND grade >= 60;
SELECT COUNT(*) INTO fail_count FROM grades WHERE year = year AND course = course AND grade < 60;
INSERT INTO student_count (year, course, pass_count, fail_count) VALUES (year, course, pass_count, fail_count);
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
```
该存储过程首先使用游标获取所有不同的年份和课程,然后使用两个 SELECT 语句分别统计及格和不及格的学生人数,并将结果插入到一个名为 student_count 的表中,该表应该包含 year、course、pass_count 和 fail_count 四个字段。
你需要根据实际情况修改表名和字段名,以及存储过程中的其他细节。