在 SQL Case 语句中正确使用嵌套的 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5396533/
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
Proper use of a nested Case statement in a SQL Case Statement
提问by Randal
Good afternoon,
下午好，
I am currently working through some postgresql code and translating it over to sql (most if it is pretty simple) but I came across a case statement with an array in it and can't figure out the proper syntax as I've never seen a case statement used in this way before.
我目前正在处理一些 postgresql 代码并将其转换为 sql（如果它很简单的话，大多数情况下）但是我遇到了一个带有数组的 case 语句，并且无法找出正确的语法，因为我从未见过以前用这种方式的case语句。
I've made a quick example of what i'm trying to do but it's still throwing a syntax error:
我已经做了一个我正在尝试做的快速示例，但它仍然抛出一个语法错误：
Select field3, field 4,
Case
When field in (1, 3, 7) then 1
When field in (2, 4, 6) then 2
When field in (5, 9) then 3
When field is null or ' ' then 4
Else
Case
When field2 = x then 1
When field2 = y then 2
Else End
End as fieldname
Here is the original code so youc an see what i'm editing it from and to. The case statement is (so far as I can tell as I mentioned earlier I have never used case in this manner) using 2 fields to get the desired results. Take note I didn't write this originally and am only porting it from postgresql to t-sql.
这是原始代码，因此您可以查看我正在编辑的内容。case 语句是（据我所知，正如我之前提到的，我从未以这种方式使用 case）使用 2 个字段来获得所需的结果。请注意，我最初没有写这个，只是将它从 postgresql 移植到 t-sql。
CASE
WHEN rank IN (1,7,9) THEN '1'
WHEN rank IN (2,5,10) THEN '2'
WHEN rank IN (3,6) THEN '3'
WHEN rank IN (4,8) THEN '4'
WHEN tier IS NULL OR tier = '' THEN 'N/A' ELSE
CASE WHEN tier = 'HE' THEN '3'
WHEN tier = 'ME' THEN '2'
WHEN tier = 'LE' THEN '1' END
END AS tier
After working in the answers below (one of them was a typo on my part) I am now getting a syntax error on the "Else End" clause.
在完成下面的答案之后（其中一个是我的错字），我现在在“Else End”子句中遇到语法错误。
I modified the question to state it's a question about a nested case statement and not an array thanks
我修改了这个问题，说明这是一个关于嵌套 case 语句而不是数组的问题，谢谢
回答by Amy B
Case
When field in (1, 3, 7) then 1
When field in (2, 4, 6) then 2
When field in (5, 9) then 3
When field is null or ' ' then Case
When field2 = x then 1
When field2 = y then 2
End --inner case
Else null
End as fieldname
am I misusing the term 'array' in this example?
我在这个例子中滥用了术语“数组”吗？
Yes.
是的。
回答by Joe Stefanelli
- You're missing a THEN for the
When field is null or ' 'case. - That case mentioned above should be written as
When field is null or field = ' ' - You're missing an END for the inner CASE statement within the ELSE.
- 你缺少一个 THEN
When field is null or ' '。 - 上面提到的那个case应该写成
When field is null or field = ' ' - 您在 ELSE 中缺少内部 CASE 语句的 END。
回答by onedaywhen
The ELSEclauses look to be redundant:
这些ELSE条款看起来是多余的：
CASE
WHEN field IN (1, 3, 7) THEN 1
WHEN field IN (2, 4, 6) THEN 2
WHEN field IN (5, 9) THEN 3
WHEN field IS NULL THEN 4
WHEN field2 = x THEN 1
WHEN field2 = y THEN 2
END AS fieldname
