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

小编典典

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

sql

第一次查询

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

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

第二查询

这是行不通的，引擎在抱怨Subquery returns more than 1 row，当-根据我-当我在做完全相同的事情时@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;

有人看到引擎为什么对第一个查询不满意时抱怨第二个查询吗？

提前致谢，

阅读 348

2021-04-14

共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;

2021-04-14