Subquery返回多行错误：已尝试IN/Join仍未解决，求技术排查

问题根源 #

你遇到的「Subquery returns more than one row」错误，核心原因是把多行结果赋值给单个变量：

SET @result:=(SELECT * FROM temp);

临时表temp里存了多个用户ID，这行代码试图把所有ID塞进一个变量，MySQL直接触发报错。而且就算没报错，后续IN(@result)也只会把变量当成单个值处理，根本达不到多ID匹配的效果。

另外你的JSON数组转临时表用了WHILE循环，写法繁琐且效率低；子查询里的UNION ALL逻辑重复，还有IF函数最后分支用空字符串会导致语法错误。

修复方案 #

1. 简化JSON数组转临时表（MySQL 8.0+）

不用写WHILE循环，直接用JSON_TABLE一次性解析JSON数组：

DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp AS
SELECT JSON_UNQUOTE(jt.memberId) AS memberId
FROM JSON_TABLE(
    p_membersList,
    '$[*]' COLUMNS(memberId INT PATH '$')
) AS jt;

如果是MySQL 5.7版本，保留WHILE循环即可，但绝对不要把多行结果赋值给单个变量。

2. 移除错误的变量赋值，直接引用临时表

所有用到IN(@result)的地方，替换成IN(SELECT memberId FROM temp)，大表场景下用JOIN替代IN子查询性能会更好。

3. 修复IF函数语法错误

原代码最后一个IF分支返回空字符串""，会导致WHERE子句语法错误，改成1=1表示不添加额外过滤条件。

4. 优化子查询逻辑

合并UNION ALL里的重复逻辑，去掉不必要的GROUP BY（如果不需要按user_id分组去重，可直接删除）。

修改后的完整代码 #

-- 1. 用JSON_TABLE快速解析JSON数组到临时表（MySQL 8.0+）
DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp AS
SELECT JSON_UNQUOTE(jt.memberId) AS memberId
FROM JSON_TABLE(
    p_membersList,
    '$[*]' COLUMNS(memberId INT PATH '$')
) AS jt;

-- 2. 统计去重后的property_id总数
SELECT COUNT(DISTINCT property.property_id) AS totalCount
FROM `property` AS property
INNER JOIN `user_additional_data` AS uad 
    ON uad.user_id = property.user_id
WHERE 
    -- 直接引用临时表的用户ID，替代错误的变量
    property.user_id IN (SELECT memberId FROM temp)
    OR property.property_id IN (
        SELECT pm.property_id
        FROM `property_member` AS pm
        LEFT JOIN `property` AS p 
            ON p.user_id = pm.user_id
        WHERE 
            pm.user_id IN (SELECT memberId FROM temp)
            OR pm.created_by IN (SELECT memberId FROM temp)
        -- 不需要按user_id分组的话可删除此行
        GROUP BY pm.property_id, pm.user_id
    )
    -- 修复IF函数的最后分支，用1=1替代空字符串
    AND IF(p_filter1 = '1', 
        (property.is_active = '1' AND property.is_draft = '0' AND property.is_deleted = '0'),
        IF(p_filter1 = '2', 
            (property.is_draft = '1' AND property.is_active = '0' AND property.is_deleted = '0'),
            IF(p_filter1 = '3', 
                (property.is_active = '0' AND property.is_draft = '0' AND property.is_deleted = '0'),
                IF(p_filter1 = '4', 
                    ((property.is_active = '1' OR property.is_draft = '1') AND property.is_deleted = '0'),
                    1=1 -- 无过滤时返回true
                )
            )
        )
    )
    AND (
        uad.is_primary_owner = '0'
        OR (uad.is_primary_owner <> '0' AND property.created_by IN (SELECT memberId FROM temp))
        OR (uad.is_primary_owner <> '0' AND property.created_by = uad.primary_owner)
        OR (uad.is_primary_owner = '1' AND uad.is_enterprise_code = '1')
    );

额外优化建议 #

• 大表场景下，把IN(SELECT memberId FROM temp)改成JOIN temp ON property.user_id = temp.memberId，性能会更优。
• 给property_member表的user_id和created_by字段加索引，能大幅提升子查询速度。
• 若不需要按user_id分组，子查询里的GROUP BY可以直接删除，避免不必要的计算。

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