MySQL错误码1242排查：子查询返回多行的查询语句问题

错误原因分析 #

你的查询触发1242 - Subquery returns more than 1 row错误，核心问题出在case语句的else分支：

select distinct role from lprovider

这个子查询会返回表中所有不同的角色（比如ExternalHealthCoach、InternalHealthCoach等），也就是多行结果，但case语句的每个分支只能返回单个值，同时in子句里嵌套这种返回多行的子查询也会导致逻辑混乱——更关键的是，你的原查询没有针对provider_id=63的角色做判断，而是遍历了整个表的行，完全偏离了需求。

符合需求的正确写法 #

根据你的需求：当provider_id=63拥有ExternalHealthCoach角色时，仅返回该角色；如果没有这个角色，返回该provider的所有角色。这里提供两种简洁高效的写法：

写法一：用EXISTS做条件判断

SELECT role
FROM lprovider
WHERE provider_id = 63
AND (
    -- 如果该provider有ExternalHealthCoach角色，只筛选这个角色
    (EXISTS (SELECT 1 FROM lprovider WHERE provider_id = 63 AND role = 'ExternalHealthCoach') AND role = 'ExternalHealthCoach')
    -- 否则返回所有角色
    OR NOT EXISTS (SELECT 1 FROM lprovider WHERE provider_id = 63 AND role = 'ExternalHealthCoach')
);

写法二：用CASE表达式简化逻辑

SELECT role
FROM lprovider
WHERE provider_id = 63
AND role = CASE
    -- 检查该provider是否存在ExternalHealthCoach角色
    WHEN EXISTS (SELECT 1 FROM lprovider WHERE provider_id = 63 AND role = 'ExternalHealthCoach')
    THEN 'ExternalHealthCoach' -- 存在则只匹配这个角色
    ELSE role -- 不存在则匹配自身，即返回所有角色
END;

逻辑验证 #

• 如果provider_id=63的角色包含ExternalHealthCoach，两个查询都会只返回这一条角色记录；
• 如果该provider没有ExternalHealthCoach角色，会返回他所有的角色（比如InternalHealthCoach、Doctor等）。

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