MySQL中使用While Loop结合连接与子查询查询数据报错求助

问题原因分析 #

你遇到的报错主要来自两个核心问题：

• MySQL中DECLARE关键字只能在**存储程序（存储过程、函数、触发器、事件）**的开头声明变量，不能直接在普通SQL会话中单独使用
• 原始逻辑存在缺陷：COUNT(DISTINCT exam_id)拿到的是不同exam_id的总数量，并不是exam_id的最大值，直接从0循环到总数量，大概率会匹配不到实际存在的exam_id，返回大量空结果

方案1：修正后的存储过程写法（保留循环逻辑） #

如果你确实需要用循环实现，先创建存储过程再调用即可。如果你的exam_id是连续自增的，可以用以下写法：

-- 临时修改语句分隔符，避免存储过程内的分号提前终止语句
DELIMITER //
CREATE PROCEDURE GetStudentScoresByExam()
BEGIN
    DECLARE cn INT DEFAULT 0;
    DECLARE max_exam_id INT DEFAULT 0;
    -- 这里获取exam_id的最大值而不是计数
    SELECT MAX(exam_id) INTO max_exam_id FROM mais_writtenworks_detail;

    WHILE cn <= max_exam_id DO
        SELECT 
            ms.`first_name`,
            ms.`middle_name`, 
            ms.`last_name`, 
            rr.ww_score
        FROM mais_students ms
        LEFT JOIN (
            SELECT mwd.ww_score, mwd.`student_id`
            FROM mais_writtenworks_detail mwd
            WHERE mwd.`exam_id` = cn
        ) AS rr ON ms.`ID` = rr.`student_id`;
        SET cn = cn + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL GetStudentScoresByExam();

如果你的exam_id不连续，建议用游标遍历所有唯一的exam_id，逻辑更严谨：

DELIMITER //
CREATE PROCEDURE GetStudentScoresByExam()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_exam_id INT;
    -- 定义游标遍历所有唯一的exam_id
    DECLARE exam_cursor CURSOR FOR SELECT DISTINCT exam_id FROM mais_writtenworks_detail;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN exam_cursor;
    exam_loop: LOOP
        FETCH exam_cursor INTO current_exam_id;
        IF done THEN
            LEAVE exam_loop;
        END IF;
        SELECT 
            ms.`first_name`,
            ms.`middle_name`, 
            ms.`last_name`, 
            rr.ww_score,
            current_exam_id as exam_id -- 新增列区分是哪次考试的成绩
        FROM mais_students ms
        LEFT JOIN (
            SELECT mwd.ww_score, mwd.`student_id`
            FROM mais_writtenworks_detail mwd
            WHERE mwd.`exam_id` = current_exam_id
        ) AS rr ON ms.`ID` = rr.`student_id`;
    END LOOP;
    CLOSE exam_cursor;
END //
DELIMITER ;

CALL GetStudentScoresByExam();

方案2：更优的无循环写法（推荐） #

2.1 明细查询写法 #

你的需求完全不需要循环，直接关联exam_id字段即可，单次查询就能拿到所有学生的所有考试成绩：

SELECT 
    ms.`first_name`,
    ms.`middle_name`, 
    ms.`last_name`,
    mwd.exam_id,
    mwd.ww_score
FROM mais_students ms
LEFT JOIN mais_writtenworks_detail mwd 
    ON ms.`ID` = mwd.`student_id`
-- 可自定义过滤条件，比如 WHERE mwd.exam_id IN (1,3,5)
ORDER BY mwd.exam_id, ms.last_name;

2.2 动态行转列写法（匹配你之前的静态写法需求） #

如果需要把每次考试的成绩作为单独列展示，且exam_id是动态的，可以用动态SQL实现，完全不需要循环：

SET @sql = NULL;
-- 动态拼接每个exam_id对应的成绩列
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN mwd.exam_id = ', exam_id, ' THEN mwd.ww_score END) AS exam_', exam_id, '_score')
) INTO @sql FROM mais_writtenworks_detail;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT ms.first_name, ms.middle_name, ms.last_name, ', @sql, ' 
FROM mais_students ms
LEFT JOIN mais_writtenworks_detail mwd ON ms.ID = mwd.student_id
GROUP BY ms.ID, ms.first_name, ms.middle_name, ms.last_name');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

内容的提问来源于stack exchange，提问作者Kemmy