带有 Exists 的 WHERE CASE WHEN 语句

Question

我正在创建一个具有 WHERE CASE WHEN 语句的 SQL 查询。我做错了什么并出错。

我的 SQL 语句就像

DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)

INSERT INTO @Areas SELECT AreaId
FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)

SELECT *
FROM dbo.CompanyMaster
WHERE AreaId IN
     (CASE WHEN EXISTS (SELECT BusinessId
                        FROM dbo.AreaSubscription
                        WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
             THEN @AreaId
             ELSE (SELECT [@Areas].AreaId FROM @Areas)
      END)

我收到错误

消息 512，第 16 级，状态 1，第 11 行

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

请帮助成功运行查询。我的逻辑是检查每行（语句）中的条件 AreaId 。

我只想在

1. 公司有订阅条目 AreaSubscription 用于通过的特定区域 @AreaId
2. 表 AreaSubscription 没有订阅条目然后评估 AreaId 在 (SELECT [@Areas].AreaId FROM @Areas)

原文由 Haresh Ambaliya 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这可能会对您有所帮助。

 SELECT * FROM dbo.CompanyMaster
WHERE AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
                   FROM dbo.AreaSubscription
                   WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
      THEN @AreaId ELSE AreaId END)
AND  AreaId IN (SELECT [@Areas].AreaId FROM @Areas)

另一种解决方案是

SELECT * FROM dbo.CompanyMaster A
LEFT JOIN @Areas B ON A.AreaId=B.AreaID
WHERE A.AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
                   FROM dbo.AreaSubscription
                   WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
      THEN @AreaId ELSE B.AreaId END)
)

原文由 Nithesh Narayanan 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

尝试这个

DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)

INSERT INTO @Areas
SELECT AreaId FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)

    IF EXISTS (SELECT BusinessId
        FROM dbo.AreaSubscription
        WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
    BEGIN
        SELECT * FROM dbo.CompanyMaster
        WHERE AreaId IN (@AreaId)
    END
    ELSE
    BEGIN
        SELECT * FROM dbo.CompanyMaster
        WHERE AreaId IN (SELECT [@Areas].AreaId FROM @Areas)
    END

原文由 bvr 发布，翻译遵循 CC BY-SA 3.0 许可协议