mysql - 在 CASE WHEN 语句中进行子选择时，子查询返回多于 1 行

Question

第一次查询

这个查询工作正常，引擎没有提示

SELECT id 
FROM agencies
WHERE id IN ((SELECT id FROM agencies))
ORDER BY id;

第二次查询

这个不起作用，引擎提示 子查询返回超过 1 行，当 - 根据我的说法 - 当 @param_1 IS NULL

SELECT 
  @param_1 := NULL,

SELECT id 
FROM agencies
WHERE id IN (CASE WHEN @param_1 IS NULL THEN (SELECT id FROM agencies) ELSE 1 END )
ORDER BY id;

有没有人明白为什么引擎提示第二个查询而不是第一个查询？

提前致谢

Answer 1

CASE 需要一个标量、单个值。不是记录集。

SELECT id 
FROM agencies
WHERE id IN (
        SELECT id FROM agencies WHERE @param_1 IS NULL
        UNION ALL
        SELECT 1 WHERE @param_1 IS NOT NULL
        )
ORDER BY id;

或

SELECT id 
FROM agencies
WHERE id IN (SELECT id FROM agencies)
     AND @param_1 IS NULL
UNION ALL
SELECT id 
FROM agencies
WHERE @param_1 IS NOT NULL AND id = 1
ORDER BY id;

另一种选择是使用 IF

IF @param_1 IS NULL
    SELECT id 
    FROM agencies
    WHERE id IN (SELECT id FROM agencies)
    ORDER BY id;
ELSE
    SELECT id 
    FROM agencies
    WHERE id = 1
    ORDER BY id;