oracle 多个 THEN 到单个 CASE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16309443/
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
Multiple THEN to a single CASE statement?
提问by user2213892
I want to add an extra column. This extra column will get a value based on a case statment. My objective is for instance, although the syntax is very wrong, something to accomplish this: When 'A' then 'Apple' AND 'ExtraColumn'=1. I have tried to create an equvialnt to this using the code below, but I get an error that my SQL syntax is incorrect.
我想添加一个额外的列。这个额外的列将根据 case 语句获得一个值。例如，我的目标是，虽然语法非常错误，但可以实现这一点：当 'A' then 'Apple' AND 'ExtraColumn'=1 时。我曾尝试使用下面的代码创建一个等效项，但我收到一个错误，指出我的 SQL 语法不正确。
select Patient,
case ColumnName
when 'A' then 'Apple'
when 'B' then 'Banana'
end ColumnName,
case ExtraColumn
when 'A' then '1'
when 'B' then '2'
end ExtraColumn,
ColumnResult
from TABLE
unpivot
(
ColumnResult
for ColumnName in (COL1,COL2,COL3)
for ExtraColumn in (COL1,COL2,COL3)
)u
回答by Dan Bracuk
You have to repeat your case construct for each column name. Something like this:
您必须为每个列名重复您的案例构造。像这样的东西：
case ColumnName
when 'A' then 'Apple'
when 'B' then 'Banana'
end ColumnName,
case ColumnName
when 'A' then '1'
when 'B' then '2'
end ExtraColumn,
There is a gotcha here. If you use ColumnName in your where clause, you might not like the results because you used it as an alias.
这里有一个问题。如果您在 where 子句中使用 ColumnName，您可能不喜欢结果，因为您将其用作别名。
Edit starts here
编辑从这里开始
You can make your aliases whatever you want. If they are simple, just type them.
您可以随心所欲地制作别名。如果它们很简单，只需键入它们。
select column1 fred, column2 barney
If you want more than one word, or an sql keyword, use double quotes
如果你想要多个单词，或者一个 sql 关键字，请使用双引号
select column1 "fred flinstone", column2 "select"
回答by haki
you can use decode
您可以使用 decode
select decode(ColumnName ,'A', 'Apple','B','Banana', 'Unknown') from ....
