在 Oracle 中的 Select 语句中使用 Case 返回多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/700658/
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
returning multiple columns using Case in Select Satement in Oracle
提问by Dinesh Manne
I have a sceanrio where i need to retreive values from different sub queries based on a condition in a main select statement. i was trying to use Case, but the problem is that Case does not support multiple columns. Is there any work around to this, or is there any other way to acheive this.
我有一个场景，我需要根据主选择语句中的条件从不同的子查询中检索值。我试图使用 Case，但问题是 Case 不支持多列。有没有办法解决这个问题，或者有没有其他方法可以实现这一点。
My scenario in a simplified query
我在简化查询中的场景
select col1,col2,
case when col3='E01089001' then
(select 1,3 from dual)
else
(select 2,4 from dual)
end
from Table1
where col1='A0529';
回答by Dave Costa
Here's another way of writing it which may address concerns about accessing the second table more times than necessary.
这是另一种编写它的方法，它可以解决对访问第二个表的次数超过必要的担忧。
select col1,col2,
case when col3='E01089001' then 1 else 2 end,
case when col3='E01089001' then 3 else 4 end
end
from Table1, dual
where col1='A0529';
Your example uses an obviously simplified subquery, so this version looks kind of silly as-is; there's no reason to join with DUAL at all. But in your real query you presumably have a subquery like SELECT a, b FROM otherTable WHERE someCondition. So you would want to use the actual column names instead of numeric literals and the actual table name instead of dual, and move the subquery conditions into the final WHERE clause.
你的例子使用了一个明显简化的子查询，所以这个版本看起来有点傻；完全没有理由加入 DUAL。但是在您的实际查询中，您大概有一个像SELECT a, b FROM otherTable WHERE someCondition. 因此，您可能希望使用实际的列名而不是数字文字和实际的表名而不是双，并将子查询条件移动到最终的 WHERE 子句中。
回答by Yas
A quick and dirty solution.
一个快速而肮脏的解决方案。
select dummy,substr(c,1,instr(c,',')-1) c1,substr(c,instr(c,',')+1) c2
from (
select dummy,
case when dummy='X' then
(select 1||','||3 from dual)
end c
from (select * from dual)
)
回答by paxdiablo
If each case only allows one column, then you probably need two cases:
如果每种情况只允许一列，那么您可能需要两种情况：
select col1,col2,
case when col3='E01089001' then
(select 1 from dual)
else
(select 2 from dual)
end,
case when col3='E01089001' then
(select 3 from dual)
else
(select 4 from dual)
end
from Table1
where col1='A0529';
I hope I don't need to tell you that this sort of stuff doesn't scale very well when the database tables become large.
我希望我不需要告诉你，当数据库表变大时，这种东西不能很好地扩展。
回答by Khb
Case does support multiple columns in the conditional check
Case 确实支持条件检查中的多列
CASE WHEN A=X AND B=Y THEN ... END
What you are trying to do in your example is return a table (2 columns) into a resultset that expects one column: col1, col2, (col3,col4).
您在示例中尝试做的是将表（2 列）返回到需要一列的结果集中：col1、col2、(col3,col4)。
You need to return them separately: col1, col2, col3, col4
您需要分别返回它们：col1、col2、col3、col4
select
col1,
col2,
case when col3='E01089001' then (select 1 from dual) else (select 3 from dual) end,
case when col3='E01089001' then (select 2 from dual) else (select 4 from dual) end
from Table1 where col1='A0529';
回答by Sridhar Sarnobat
The best approach for me is to use REGEXP_REPLACE. Have a single string returned from the case statement, and in the outer query block select statement tokenize the string into different fields.
对我来说最好的方法是使用 REGEXP_REPLACE。从 case 语句返回单个字符串，并在外部查询块 select 语句中将该字符串标记为不同的字段。
