MySQL存储过程运行报错Subquery returns more than 1 row问题求助

DevOps 小满

2026-10-2

报错根因

Subquery returns more than 1 row报错的核心原因是IF判断条件中的子查询未做聚合或存在性校验，当同一个部门下存在多条符合筛选条件的记录时，子查询会返回多行结果，无法直接和数值0做比较，直接触发报错。

原代码存在的其他问题

子查询逻辑不符合需求：原本要判断对应部门是否存在对应优先级的用户，但原写法是返回所有符合条件的部门id值，而非判断是否存在匹配项
结果查询无过滤条件：IF判断通过后执行的查询没有加部门id过滤，会返回全表的用户数据，完全不符合按传入部门id查询的需求
逻辑冗余：优先级参数的判断重复写在每个子查询中，增加了不必要的查询开销

修正后的存储过程代码

PROCEDURE `get_parent`(in id_dept int, in id_priorty int )
BEGIN
    -- 优先级3查员工表
    IF id_priorty = 3 AND EXISTS(SELECT 1 FROM employee_tb WHERE id_dept = id_dept) THEN
        SELECT id_employee, full_name, 3 AS type FROM employee_tb WHERE id_dept = id_dept;
    -- 优先级2查组长表
    ELSEIF id_priorty = 2 AND EXISTS(SELECT 1 FROM leader_tb WHERE id_dept = id_dept) THEN
        SELECT id_leader, full_name, 2 AS type FROM leader_tb WHERE id_dept = id_dept;
    -- 优先级1查经理表
    ELSEIF id_priorty = 1 AND EXISTS(SELECT 1 FROM manager_tb WHERE id_dept = id_dept) THEN
        SELECT id_manager, full_name, 1 AS type FROM manager_tb WHERE id_dept = id_dept;
    ELSE
        SELECT 'failed' AS result;
    END IF;
END