如何在 WHERE 子句中重用计算的 CASE 列? [英] How can I reuse a calculated CASE column in the WHERE clause?

问题描述

我有一个疑问:

SELECT id
    , CASE WHEN id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END AS amberRed
FROM db.tbl1

我想将某些内容归类为 red 或 amber 并排除其他所有内容.

I want to either classify something as red or amber and exclude everything else.

这背后的原因是因为如果我不排除 NULLs，结果数据集的深度为 144,000,000(1.44 亿)行；如果排除 NULLs，它会将其减少到仅仅 878,000.

The reason behind this is because if I don't exclude NULLs, the resulting dataset is 144,000,000 (144 million) rows deep; if the NULLs are excluded, it whittles it down to a mere 878,000.

我一直在阅读的一般建议是尝试这种方法，但它不起作用，因为列 amberRed 未被识别:

The general advice that I keep reading is to try this approach but it doesn't work as the column amberRed isn't recognised:

SELECT id
    , CASE WHEN id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END AS amberRed
FROM db.tbl1
WHERE amberRed IS NOT NULL

我怎样才能做到这一点?

How can I achieve this?

推荐答案

在 SQL Server 中，您无法在 WHERE 子句中使用计算列，只能在 ORDER BY子句.

In SQL Server you are unable to use a calculated column in the WHERE clause, only in the ORDER BY clause.

因此您要么需要某种形式的子查询，要么必须重复计算.CROSS APPLY 是实现此目的的巧妙方法.

So you either need a sub-query of some form or you have to repeat the calculation. CROSS APPLY is a neat way to accomplish this.

SELECT T1.id, X.AmberRed
FROM db.tbl1 T1
CROSS APPLY (VALUES (
    CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END
)) AS X (AmberRed)
WHERE X.AmberRed IS NOT NULL;

但是一个简单的子查询也可以完成这项工作

But a simple sub-query will also do the job

SELECT X.id, X.AmberRed
FROM (
    SELECT T1.id
        , CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
        WHEN TestReason = 'itf' THEN 'red'
        ELSE NULL END
    FROM db.tbl1 T1
) X
WHERE X.AmberRed IS NOT NULL;

或者你甚至可以简单地重复这个表达式:

Or you can even just repeat the expression if its simple:

SELECT T1.id
    , CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END
FROM db.tbl1 T1
WHERE CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END IS NOT NULL;

这篇关于如何在 WHERE 子句中重用计算的 CASE 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！