oracle 如何从 Where 条件中的 CASE 子句返回多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31242785/
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 from CASE clause in Where Condition
提问by dev90
I have a codition in which i need to use multiple parameters in whereclause
using Conditional operator.
I have written the following query and in this condition i can't use inclasue.
Kindly guide me how can i return multiple parameters from case clause.
我有一个代码，我需要在where使用条件运算符的子句中使用多个参数。我已经编写了以下查询，在这种情况下我不能使用inclasue。请指导我如何从 case 子句中返回多个参数。
select * from cardimport
where
STATUS = CASE
WHEN STATUS = ''
THEN 'F'
ELSE STATUS
END
When Status in nulli want to return 'F'and 'V'. Right now its returning Only 'F'
当 Status 在null我想返回'F'和'V'. 现在它只返回'F'
EDITED
已编辑
select *
from CARDIMPORT
where STATUS = CASE
WHEN $P{status} = ''
THEN 'E'
ELSE $P{status}
END
When the STATUS is null, I want to show the records of from all status, that are 'E', 'I', 'A'
当 STATUS 为空时，我想显示来自所有状态的记录，即'E', 'I','A'
采纳答案by Emipro Technologies Pvt. Ltd.
You should try following,
你应该尝试跟随，
Declare @status nvarchar(50) = 'XXXX'
select
*
from cardimport
where
1 = case when isnull(@status,'') = '' then 1
else
case when status = @status then 1 else 0 end
end
It will give you all the rows when status is null and when status is not null then give you only matching data.
当状态为空时，它会给你所有的行，当状态不为空时，只给你匹配的数据。
回答by Lalit Kumar B
I think you don't need CASE, rather you need NVLand NVL2.
我认为您不需要 CASE ，而是需要NVL和NVL2。
If I understand your requirement correctly, you need a particular row when the status value is NULL, else when not null then return all the rows.
如果我正确理解您的要求，当状态值为 NULL 时，您需要一个特定的行，否则当不为 null 时返回所有行。
For example, in the below example from EMPtable. If the parameter value is NULL, I expect only that row where comm= 300else when not nullthen return all rows.
例如，在下EMP表中的示例中。如果参数值为NULL，则我只期望该行 where comm= 300else 时not null返回所有行。
NULL
空值
SQL> VAR a NUMBER;
SQL> EXEC :a := NULL
PL/SQL procedure successfully completed.
SQL> SELECT empno, comm FROM emp WHERE NVL(comm, 9999) = NVL2(:a, NVL(comm, 9999), 300);
EMPNO COMM
---------- ----------
7499 300
NOT NULL
非空
SQL> EXEC :a :=1400
PL/SQL procedure successfully completed.
SQL> SELECT empno, comm FROM emp WHERE NVL(comm, 9999) = NVL2(:a, NVL(comm, 9999), 300);
EMPNO COMM
---------- ----------
7369
7499 300
7521 500
7566
7654 1400
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
SQL>
The above uses the following logic:
以上使用了以下逻辑：
- If, the value passed is NULL, then return row where
COMM = 300. - Else if, value passed is NOT NULL, then return all the rows.
- 如果，传递的值为 NULL，则返回行 where
COMM = 300。 - 否则，如果传递的值不是 NULL，则返回所有行。
回答by Maneshwar
I am not sure about doing it in the ORACLEbut the same can be achieved in Microsoft SQL SERVERby the following method:
我不确定是否要这样做，ORACLE但可以Microsoft SQL SERVER通过以下方法实现：
DECLARE @Status VARCHAR(1)=''
SELECT * from cardimport
where
STATUS IN(SELECT Status FROM cardimport WHERE (@Status='' AND status IN('E','I','A')) OR (@Status<>'' AND status=status))
回答by Arkadiusz ?ukasiewicz
Is possible to return collection from case.
KU$_VCNT is predefined collection (KU$_VCNT is table of varchar2(4000)).
可以从案例中返回集合。KU$_VCNT 是预定义的集合 ( KU$_VCNT is table of varchar2(4000))。
with c_table as (select null status from dual
union all
select 'A' status from dual
union all
select 'B' status from dual
)
select status,case when status is null then KU$_VCNT('NULL1','NULL2') else KU$_VCNT('A','B','C','D') end from c_table
where status member of
case when status is null then KU$_VCNT('NULL1','NULL1') else KU$_VCNT('A','B','C','D') end
回答by 027
select * from cardimport t
where
t.STATUS in
case when t.STATUS is null then
(select 'F' from dual union select 'V' from dual ) else t.STATUS end
This kind of the condition is require. But i find that there may be something wrong in your understanding because you tried to match the null status with 'F'/'V' which will never turn into true condition
这种条件是必须的。但我发现您的理解可能有问题，因为您试图将空状态与 'F'/'V' 匹配，这永远不会变成真正的条件
Above is just the condition if you want to have in clause with case when that return multiple records
以上只是条件，如果您想在返回多条记录时使用带有 case 的子句
