在 CASE WHEN 语句中进行子选择时，子查询返回多于 1 行 [英] Subquery returns more than 1 row when subselecting in a CASE WHEN statement

问题描述

第一次查询

这个查询工作正常，引擎没有抱怨

This query works fine and the engine does not complain about it

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

第二次查询

这个不起作用，引擎抱怨Subquery返回超过1行，当-根据我-当@param_1 IS NULL<时，我正在做完全相同的事情

This one does not work, the engine is complaining about Subquery returns more than 1 row, when -according to me- I am doing the exact same thing when @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;

有没有人明白为什么引擎会抱怨第二个查询而不是第一个查询?

Does anybody see why the engine is complaining about the second query when it isnt for the first query ?

提前致谢，

推荐答案

CASE 需要一个单一的标量值.不是记录集.

CASE expects a scalar, single value. Not a record set.

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

Another choice is to use 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;

这篇关于在 CASE WHEN 语句中进行子选择时，子查询返回多于 1 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！