为什么我不能在 SQL Server 的 CASE 语句列上使用过滤器我的 WHERE 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24063177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I use filter my WHERE clause on a CASE statement column in SQL Server?
提问by LaRae White
In my select statement there is a CASE WHEN THEN ELSE END AS statement that I am not able to filter on in my WHERE clause. I do not see why this would be an issue, could someone shed some light?
在我的 select 语句中有一个 CASE WHEN THEN ELSE END AS 语句，我无法在我的 WHERE 子句中对其进行过滤。我不明白为什么这会是一个问题，有人可以解释一下吗？
SELECT
CASE
WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA'
WHEN m.Country IN ('BRAZIL') THEN 'JD2'
WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ'
ELSE 'Unknown'
END AS DerivedRegion,
m.ID,
m.[Account Name],
m.[Display Name],
m.[Last Name],
m.[First Name]
FROM dbo.Users AS m
WHERE DerivedRegion = 'Unknown'
There WHERE clause gives me the error: Invalid column name 'DerivedRegion', why?
有 WHERE 子句给了我错误：无效的列名 'DerivedRegion'，为什么？
回答by cadrell0
WHEREis processed before SELECT. It doesn't know what DerviedRegionis at that point. I'd recommend using a NOT INin this case to exclude the list of countries. However, if you really want to use your CASE you could do something like this
WHERE之前处理过SELECT。它不知道那个时候DerviedRegion是什么。我建议NOT IN在这种情况下使用 a来排除国家/地区列表。但是，如果你真的想使用你的 CASE 你可以做这样的事情
SELECT *
FROM
(
SELECT
CASE
WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA'
WHEN m.Country IN ('BRAZIL') THEN 'JD2'
WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ'
ELSE 'Unknown'
END AS DerivedRegion,
m.ID,
m.[Account Name],
m.[Display Name],
m.[Last Name],
m.[First Name]
FROM dbo.Users AS m
) AS x
WHERE x.DerivedRegion = 'Unknown'
Check out MSDNand scroll down to Logical Processing Order of the SELECT statementto see the order in which a query is processed.
查看MSDN并向下滚动到SELECT 语句的逻辑处理顺序以查看处理查询的顺序。
回答by Oliver Apel
You should repeat all in′s in the whereclause:
你应该in在where子句中重复所有的's ：
SELECT
CASE
WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA'
WHEN m.Country IN ('BRAZIL') THEN 'JD2'
WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ'
ELSE 'Unknown'
END AS DerivedRegion,
m.ID,
m.[Account Name],
m.[Display Name],
m.[Last Name],
m.[First Name]
FROM
dbo.Users AS m
WHERE
m.Country NOT IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO', 'BRAZIL', 'NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA')
Other solution could be: save the query as view without the where-clause (eg "myview") and then select from the view:
其他解决方案可能是：将查询保存为没有where-clause 的视图（例如“myview”），然后从视图中选择：
SELECT * FROM myview WHERE DerivedRegion = 'Unknown'
In this case, SQL server can better plan the execution.
在这种情况下，SQL Server 可以更好地计划执行。
