SQL Server 尝试在连接中使用Case表达式的结果..需要改进查询

rjzwgtxy  于 2022-12-17  发布在  其他
关注(0)|答案(1)|浏览(107)

我有以下查询,它允许我基于TransactionClassID从主表(Transactions)或TransactionRules中连接TransactionClass表,条件如下:

SELECT
    Description,
    TC.Name,
    (CASE
         WHEN (TR.TransactionRuleId > 0)
         THEN TR.TransactionRuleId
         ELSE T.TransactionClassId
    END) As ClassId
FROM Transactions AS T
LEFT JOIN TransactionRules TR ON T.Description LIKE TR.Pattern
LEFT JOIN TransactionClasses TC ON TC.TransactionClassId =
                                (CASE
                                    WHEN (TR.TransactionRuleId > 0)
                                    THEN TR.TransactionClassId
                                    ELSE T.TransactionClassId
                                END)

查询在SQL Server上运行。实际上,它将根据TransactionRules上的联接是否成功来检索正确的TransactionClass条目。上面的查询有效,但我正在尝试简化查询,以便不必在两个位置重复CASE表达式。
我尝试在变量中捕获case表达式的结果,并按如下方式使用:

SELECT
    Description,
    x
FROM Transactions AS T
LEFT JOIN TransactionRules TR
    ON T.Description LIKE TR.Pattern
LEFT JOIN TransactionClasses TC
    ON TC.TransactionClassId = x
WHERE x = (CASE
               WHEN (TR.TransactionRuleId > 0)
               THEN TR.TransactionRuleId
               ELSE T.TransactionClassId
           END)

但我得到的错误:
[S0001][207]第8行:列名“x”无效。
我在尝试只有一个CASE表达式时哪里出错了?

0md85ypi

0md85ypi1#

CROSS APPLY是重新使用计算值的一种简洁方式,例如

SELECT
    [Description]
    , TC.[Name]
    , Class.Id
FROM Transactions AS T
LEFT JOIN TransactionRules TR ON T.[Description] LIKE TR.Pattern
CROSS APPLY (
    VALUES (
        CASE
           WHEN TR.TransactionRuleId > 0
           THEN TR.TransactionRuleId
           ELSE T.TransactionClassId
        END
    ) 
) AS Class (Id)
LEFT JOIN TransactionClasses TC ON TC.TransactionClassId = Class.Id;

相关问题