oracle WHERE 子句中的 if 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8922862/
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
If statements in WHERE clause
提问by dan-klasson
Is it possible in Oracle to put conditional IF statements in the WHERE clause?
在 Oracle 中是否可以将条件 IF 语句放在 WHERE 子句中？
I want to filter all rows with an end date before today. And if the end date is empty, it should not filter on it. I've tried this:
我想过滤结束日期在今天之前的所有行。如果结束日期为空，则不应对其进行过滤。我试过这个：
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date
AND
IF
end_date IS NOT EMPTY
THEN
sysdate < end_date
But I get "invalid relational operator".
但我得到“无效的关系运算符”。
回答by Florin Ghita
You can try:
你可以试试：
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date
AND sysdate < nvl(end_date,sysdate+1)
回答by Korhan Ozturk
I don't think that if-else statements can be used in pure Sql code. You need to use stored procedureto achieve your aim. I suppose in your case you can use the code below:
我不认为 if-else 语句可以在纯 Sql 代码中使用。您需要使用存储过程来实现您的目标。我想在您的情况下，您可以使用以下代码：
DECLARE
DATE end_date
BEGIN
IF end_date IS NOT NULL THEN
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date AND sysdate < end_date;
END IF;
END;
回答by paxdiablo
Even if it's possible, it's not a good idea. Per-row functions will destroy performance.
即使有可能，这也不是一个好主意。每行函数会破坏性能。
In this case, the best way is to probably just union two exclusive queries:
在这种情况下，最好的方法可能只是联合两个独占查询：
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date
AND end_date IS NULL
UNION ALL
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date
AND end_date IS NOT NULL
AND sysdate < end_date
(changed to NULLfrom EMPTYsince that seems to be what you were after).
（更改为NULLfromEMPTY因为这似乎是您所追求的）。
Assuming end_dateis indexed, this should scream along even though it's two queries. Having to do some extra processing on each and every row returned is rarely a good idea.
假设end_date已编入索引，即使它是两个查询，它也应该大喊大叫。对返回的每一行都做一些额外的处理很少是一个好主意。
Whatever methods you choose to investigate, benchmark them with real world data. The prime directive of optimisation is measure, don't guess.
无论您选择哪种方法进行调查，都要使用真实世界的数据对它们进行基准测试。优化的主要指令是测量，不要猜测。
回答by Stefan
回答by Brian Willis
Couldn't you do this:
你不能这样做吗：
SELECT discount_amount
FROM vw_ph_discount_data
WHERE sysdate > start_date
AND (end_date IS EMPTY OR sysdate < end_date)
