sql - CTE 在 CASE EXIST 中返回错误值

Question

如果特定邻居存在于城市中，我想恢复一个 bool 值

城市

City_Id | State_Id | CityName
1         6          São Paulo
2         22         São Paulo
3         26         São Paulo

邻居

Neighbor_Id | City_Id | NeighborName

查询

WITH cte
AS (
    SELECT City.*
    FROM City
    WHERE (City.CityName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'são paulo'
)
SELECT cte.*
    ,CASE 
    WHEN EXISTS (
            SELECT Neighbor.City_Id 
            FROM Neighbor
            INNER JOIN cte ON Neighbor.City_Id = cte.city_id
            WHERE (Neighbor.NeighborName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'ademar'
        )
        THEN 1
        ELSE 0
    END AS F
FROM cte

结果

City_Id | State_Id | CityName   | f
1         6          São Paulo    1
2         22         São Paulo    1
3         26         São Paulo    1

错误

我知道在 City_Id 2 中，我的表中不存在具有此城市 ID 的任何邻居，那么为什么在我的 CASE EXISTS 中返回 1当城市 ID = 2 时？

Answer 1

您的问题发生是因为您在子查询中单独链接到 cte - 它不是根据主查询中特定行的 cte 检查邻居的值，而是针对 整体 cte。相反，尝试更改 F 的表达式到:

CASE 
WHEN EXISTS (
        SELECT Neighbor.City_Id 
        FROM Neighbor
        WHERE Neighbor.City_Id = cte.city_id and
              (Neighbor.NeighborName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'ademar'
    )
    THEN 1
    ELSE 0
END AS F