选择查询中的 MySQL IF ELSEIF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13887616/
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
MySQL IF ELSEIF in select query
提问by Ivan Bravo Carlos
I'm trying to select different prices of a product based on the quantity that user chooses. This is the query I'm working on (it has a syntax error):
我正在尝试根据用户选择的数量选择产品的不同价格。这是我正在处理的查询（它有一个语法错误）：
select id,
(SELECT
IF(qty_1<='23',price,1)
ELSEIF(('23'>qty_1 && qty_2<='23'),price_2,1)
ELSEIF(('23'>qty_2 && qty_3<='23'),price_3,1)
ELSEIF('23'>qty_3,price_4,1)
END IF) as total
from product;
回答by nawfal
You have what you have used in stored procedures like thisfor reference, but they are not intended to be used as you have now. You can use IFas shown by duskwuff. But a Casestatement is better for eyes. Like this:
你有你在存储过程中使用了什么这样的参考，但他们并不打算因为你现在必须使用。您可以IF按 所示使用duskwuff。但Case声明对眼睛更好。像这样：
select id,
(
CASE
WHEN qty_1 <= '23' THEN price
WHEN '23' > qty_1 && qty_2 <= '23' THEN price_2
WHEN '23' > qty_2 && qty_3 <= '23' THEN price_3
WHEN '23' > qty_3 THEN price_4
ELSE 1
END) AS total
from product;
This looks cleaner. I suppose you do not require the inner SELECTanyway..
这看起来更干净。我想你无论如何都不需要内部SELECT..
回答by duskwuff -inactive-
IF()in MySQL is a ternary function, not a control structure -- if the condition in the first argument is true, it returns the second argument; otherwise, it returns the third argument. There is no corresponding ELSEIF()function or END IFkeyword.
IF()在 MySQL 中是一个三元函数，而不是一个控制结构——如果第一个参数中的条件为真，则返回第二个参数；否则，它返回第三个参数。没有相应的ELSEIF()函数或END IF关键字。
The closest equivalent to what you've got would be something like:
最接近你所拥有的东西是这样的：
IF(qty_1<='23', price,
IF('23'>qty_1 && qty_2<='23', price_2,
IF('23'>qty_2 && qty_3<='23', price_3,
IF('23'>qty_3, price_4, 1)
)
)
)
The conditions don't all make sense to me (it looks as though some of them may be inadvertently reversed?), but without knowing what exactly you're trying to accomplish, it's hard for me to fix that.
这些条件对我来说并不全都有意义（看起来好像其中一些可能被无意中逆转了？），但不知道你到底想要完成什么，我很难解决这个问题。
回答by Dave Crooke
I found a bug in MySQL 5.1.72 when using the nested if() functions .... the value of column variables (e.g. qty_1) is blank inside the second if(), rendering it useless. Use the following construct instead:
我在使用嵌套 if() 函数时在 MySQL 5.1.72 中发现了一个错误......列变量的值（例如 qty_1）在第二个 if() 中为空，使其无用。请改用以下结构：
case
when qty_1<='23' then price
when '23'>qty_1 && qty_2<='23' then price_2
when '23'>qty_2 && qty_3<='23' then price_3
when '23'>qty_3 then price_4
else 1
end
回答by Dilraj Singh
For your question :
对于您的问题：
SELECT id,
IF(qty_1 <= '23', price,
IF(('23' > qty_1 && qty_2 <= '23'), price_2,
IF(('23' > qty_2 && qty_3 <= '23'), price_3,
IF(('23' > qty_2 && qty_3<='23'), price_3,
IF('23' > qty_3, price_4, 1))))) as total
FROM product;
You can use the if - elsecontrol structure or the IFfunction in MySQL.
您可以使用MySQL 中的if - else控制结构或IF函数。
Reference:
http://easysolutionweb.com/sql-pl-sql/how-to-use-if-and-else-in-mysql/
参考：http:
//easysolutionweb.com/sql-pl-sql/how-to-use-if-and-else-in-mysql/
回答by Warren
As per Nawfal's answer, IF statements need to be in a procedure. I found this post that shows a brilliant example of using your script in a procedure while still developing and testing. Basically, you create, call then drop the procedure:
根据 Nawfal 的回答，IF 语句需要在一个过程中。我发现这篇文章展示了在开发和测试过程中使用脚本的绝妙示例。基本上，您创建、调用然后删除过程：
