oracle oracle中如何使用case语句返回多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1714823/
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
How to return multiple values using case statement in oracle
提问by Deepak
I want to return multiple values from a query in oracle. For ex:
我想从 oracle 中的查询返回多个值。例如：
select count(*)
from tablename a
where asofdate='10-nov-2009'
and a.FILENAME in (case
when 1 = 1 then (select distinct filename from tablename
where asofdate='10-nov-2009' and isin is null)
else null
end);
I am getting error: ora 01427 single row subquery returns more than one row
我收到错误：ora 01427 单行子查询返回多于一行
Please advice.
请指教。
Thanks, Deepak
谢谢，迪帕克
回答by Thorsten
A CASE statement cannot return more than one value, it is a function working on one value.
CASE 语句不能返回多个值，它是一个处理一个值的函数。
It is not required for your statement, this statement should work:
您的声明不需要它，此声明应该有效：
select count(*)
from tablename a
where asofdate='10-nov-2009'
and a.FILENAME in (select distinct filename
from tablename
where asofdate='10-nov-2009'
and isin is null);
Maybe you have another usage scenario in mind? Something like this: Select * From aTable Where in CASE WHEN Then WHEN Then ELSE END
也许您有其他使用场景？是这样的： Select * From aTable Where in CASE WHEN Then WHEN Then ELSE END
Then using CASE may not be the right scenario. Maybe this helps you in the right direction:
那么使用 CASE 可能不是正确的场景。也许这可以帮助您朝着正确的方向前进：
Select *
From aTable
Where <Case1> and column1 in <Subselect1>
Or <Case2> and column1 in <Subselect2>
OR Not (<Case1> Or <Case2>) and column1 in <Subselect3>
But this will probably be quite some work for the optimizer ...
但这对优化器来说可能是相当多的工作......
回答by MartW
The distinctin your Case statement is attempting to return multiple values when only one is allowed, and your SELECT statement will only return one value in one row currently. If you're trying to get the count of each filename, do
Case 语句中的distinct试图在只允许一个值时返回多个值，而您的 SELECT 语句当前只会在一行中返回一个值。如果您想获取每个文件名的数量，请执行以下操作
SELECT FileName, Count(*)
FROM tablename
WHERE asofdate='10-nov-2009' and isin is null
GROUP BY FileName
回答by Andomar
For all I can tell, you're looking for something like:
据我所知，您正在寻找类似的东西：
select a.filename, count(*)
from tablename a
where a.asofdate = '10-nov-2009'
and exists (
select *
from tablename b
where b.isin is null
and a.asofdate = '10-nov-2009'
and a.filename = b.filename
)
group by a.filename
This would find the count of filenames for a day, for which there exists at least one row where isin is null.
这将找到一天的文件名计数，其中至少存在一行，其中isin is null.
If you edit your question and add an explanation of what you're looking for, you might get better answers.
如果您编辑您的问题并添加您要查找的内容的说明，您可能会得到更好的答案。
回答by Aaron Digulla
Run this query:
运行此查询：
select distinct filename from tablename
where asofdate='10-nov-2009' and isin is null
You'll see that it returns more than a single row which causes the ORA-01427.
您会看到它返回的不止一行导致 ORA-01427。
