SQL查询报错‘子查询返回多值’，如何检查拟生成学生邮箱是否重复？

你的错误根源有两个：

1. LIKE的下划线通配符问题：SQL里LIKE中的_是通配符，会匹配任意单个字符，比如你的拟议邮箱john_doe@school.edu会被LIKE匹配到johnXdoe@school.edu这类邮箱，导致子查询返回大量无关结果。
2. 子查询未限制返回行数：即使没有通配符问题，当存在多个匹配的邮箱时，子查询会返回所有匹配值，触发"Subquery returned more than 1 value"错误。

下面是几个易于理解和维护的解决方案：

方案1：直观判断是否存在冲突（推荐） #

用EXISTS判断拟议邮箱是否已被占用，返回明确的提示文本，同时用=代替LIKE避免通配符问题：

SELECT 
    firstname + '_' + lastname + '@school.edu' AS ProposedEmailAddress,
    CASE 
        -- 如果是检查新学生的拟议邮箱，把下面的StudentTable换成现有学生表，FROM换成新学生表
        WHEN EXISTS (
            SELECT 1 
            FROM StudentTable st 
            WHERE st.emailaddress = firstname + '_' + lastname + '@school.edu'
            -- 如果是在现有学生表中检查自身拟议邮箱是否和他人冲突，加上这行排除自己（假设主键是student_id）
            AND st.student_id <> StudentTable.student_id
        )
        THEN '邮箱已被占用'
        ELSE '邮箱可用'
    END AS DuplicateCheck
FROM StudentTable

方案2：返回冲突的邮箱地址 #

如果需要显示具体冲突的邮箱，用TOP 1限制子查询只返回一个结果：

SELECT 
    firstname + '_' + lastname + '@school.edu' AS ProposedEmailAddress,
    (SELECT TOP 1 st.emailaddress 
     FROM StudentTable st 
     WHERE st.emailaddress = firstname + '_' + lastname + '@school.edu'
     AND st.student_id <> StudentTable.student_id) AS DuplicateCheck
FROM StudentTable

注意事项 #

• 永远用=代替无通配符的LIKE，避免通配符带来的意外匹配。
• 如果你的场景是处理新入学学生，应该从新学生的表中查询，而不是现有学生表，避免重复检查现有学生自己的邮箱。

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