oracle 如果一个条件为真，如何选择一些行，如果另一个条件为真，如何选择其他行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18309303/
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 select some rows if one condition is true and others if another condition is true?
提问by Noah Martin
I want to select some rows from a table if a certain condition is true, then if another condition is true to select some others and else (in end) to select some other rows. The main problem is that I want to insert a parameter from command line like this:
如果某个条件为真，我想从表中选择一些行，然后如果另一个条件为真，则选择其他一些行，否则（最后）选择其他一些行。主要问题是我想从命令行插入一个参数，如下所示：
if exists(select a.* from a
left join b on a.id=b.id
where b.id=:MY_PARAMETER)
else if exists
(select c.* from c where c.id=:Another_Parameter)
else
(select * from b)
I understand that I am doing something wrong but I can not figure out what. I tried using CASE-Then but I couldn't find a way to adapt to the solution. Any idea? Thanks
我知道我做错了什么，但我不知道是什么。我尝试使用 CASE-Then 但我找不到适应解决方案的方法。任何的想法？谢谢
PS: I read some other posts about something like this but as I explained I am having difficulties through this.
PS：我阅读了其他一些关于此类内容的帖子，但正如我所解释的那样，我在这方面遇到了困难。
<===Edited=====>
<===编辑=====>
Hoping I am clarifying something:
希望我澄清一些事情：
select
case when b.id=6
then (select * from a)
else (select a.* from a join b
on b.aid=a.aid)
end
from a join b
on b.aid=a.aid
join c
on b.id=c.bid
where b.id=:num
In this case the problem is that it does not allow to return more than one value in the CASE statement.
在这种情况下，问题在于它不允许在 CASE 语句中返回多个值。
回答by Mikhail
The union should do just fine, for example for your first example (this will work only if tables a, b and c have similar column order and types):
联合应该做得很好，例如对于您的第一个示例（这仅在表 a、b 和 c 具有相似的列顺序和类型时才有效）：
select a.* from a
left join b on a.id=b.id
where b.id=:MY_PARAMETER
UNION
select c.* from c where c.id=:Another_Parameter
and not exists(select a.* from a
left join b on a.id=b.id
where b.id=:MY_PARAMETER)
UNION
select b.* from b
where not exists
(select c.* from c where c.id=:Another_Parameter
and not exists(select a.* from a
left join b on a.id=b.id
where b.id=:MY_PARAMETER))
and not exists (select a.* from a
left join b on a.id=b.id
where b.id=:MY_PARAMETER)
In order to build more effective query, I need more specific example.
为了构建更有效的查询，我需要更具体的示例。
SELECT a.* FROM a
INNER JOIN b ON a.id = b.id
WHERE b.id = :MY_PARAMETER
UNION
SELECT a.* FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.id = c.bid
WHERE NOT EXISTS (SELECT * FROM b WHERE
b.id = :MY_PARAMETER)
AND c.id = :Another_Parameter
回答by Noah Martin
Thanks from Mikhail, the right query that solved my problem is this:
感谢 Mikhail，解决我的问题的正确查询是：
select a.*
from a
join b
on a.id1=b.id2
where b.id1= :value and exists
(select b.id1 from bwhere b.id1 = :value)
union all
select *
from a
where exists (select * from c where c.id1=5 and c.id2=:value)
回答by Taemyr
Have you tried DECODE?
你试过解码吗？
Select DECODE(EXISTS(*Case1*),*First case column*,
DECODE(EXISTS(*Case2*),*Second case column*,*Default case column*)) FROM ...
