SQL 或在第一次检查后停止

Question

一旦满足条件，是否可以让 SQL 停止检查 WHERE 子句？例如，如果我有如下声明:

SELECT * FROM Table1 
WHERE Table1.SubID = (SELECT TOP 1 SubID FROM Table2 ORDER BY Date DESC) 
OR Table1.OrderID = (SELECT TOP 1 OrderID FROM Table2 ORDER BY Date DESC)

第一次检查后是否可以停止执行？本质上，只应使用 where 子句中的两个检查中的一个，优先使用第一个。下面的例子。

示例案例:

案例1

Table1 SubID=600 OrderID=5

Table2 TOP 1 SubID=NULL

Table2 TOP 1 OrderID=5

Matches the OrderID to 5

案例2

Table1 SubId=600 OrderId=5

Table2 Top 1 SubID=600

Table2 Top 1 OrderID=3

Matches to SubID=600, not OrderID=3

给出建议的答案， with似乎是解决 SQL 本身不能做的事情的最佳解决方案。对于我的具体情况，问题是在尝试将其放入 outer apply 时出现的。 ， 如下。

SELECT * FROM tbl_MainFields
OUTER APPLY
(
    WITH conditional AS
    (
        SELECT 1 AS 'choice', PlanCode, Carrier
        FROM tbl_payers
        WHERE tbl_payers.PlanCode = 
            (
                SELECT TOP 1 PlanCode 
                FROM tbl_payerDenials 
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
        UNION ALL
        SELECT 2 AS 'choice', PlanCode, Carrier
        FROM tbl_payers
        WHERE tbl_payers.OrderNum = 
            (
                SELECT TOP 1 DenialLevel
                FROM tbl_payerDenials
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
)
SELECT
    PlanCode AS DenialPC,
    Carrier AS DenialCAR
FROM conditional
WHERE choice = (SELECT MIN(choice) FROM conditional)    

) denialData

Answer 1

我想你可以尝试这样的事情

WITH conditional AS(

    SELECT 1 AS 'choice', PlanCode, Carrier
    FROM tbl_payers
    WHERE tbl_payers.PlanCode = 
        (
            SELECT TOP 1 PlanCode 
            FROM tbl_payerDenials
                JOIN tbl_mainFields ON 
                  tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
            ORDER BY InsertDate DESC
        )

    UNION ALL

    SELECT 2 AS 'choice', PlanCode, Carrier
    FROM tbl_payers
    WHERE tbl_payers.OrderNum = 
        (
            SELECT TOP 1 DenialLevel
            FROM tbl_payerDenials
                JOIN tbl_mainFields ON 
                  tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
            ORDER BY InsertDate DESC
        )
)
SELECT * FROM tbl_MainFields tMF
OUTER APPLY
(    
    SELECT * 
    FROM conditional c
    WHERE c.choice = (SELECT MIN(choice) FROM conditional)
) denialData

我正在使用 1和 2值为 mark查询，然后从第一个查询中选择信息，如果它返回值，否则从第二个查询( MIN(choice) 部分)返回值。

我希望很清楚。