MySQL 如何在MYSQL中编写嵌套if else if
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21676224/
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 write nested if else if in MYSQL
提问by user3239587
The folowing sntax seams to be correct. While running on mysql gives error
以下 sntax 接缝是正确的。在mysql上运行时出现错误
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27".
错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 27 行附近使用的正确语法。
delimiter $$
create function check2_login(p_username varchar(30),p_password varchar(30),role varchar(20))
returns bool
deterministic
begin
declare loginstatus bool default false;
if role="customer"then
select custid from customer where custid=p_username and pwd=p_password;
if !row_count()=0 then
select true into loginstatus;
end if;
else if role="executive"then
select execid from executive where execid=p_username and pwd=p_password;
if !row_count()=0 then
select true into loginstatus;
end if;
else if role="admin"then
select empid from employee where empid=p_username and pwd=p_password;
if !row_count()=0 then
select true into loginstatus;
end if;
else
return loginstatus;
end if;
return loginstatus;
end $$
回答by Ashish Jagtap
Update your stored function as follows
更新您存储的函数如下
DELIMITER $$
DROP FUNCTION IF EXISTS `check2_login`$$
CREATE FUNCTION `check2_login`(p_username VARCHAR(30),p_password VARCHAR(30),role VARCHAR(20)) RETURNS BOOL
BEGIN
DECLARE retval INT;
IF role = "customer" THEN
SELECT COUNT(custid) INTO retval FROM customer WHERE custid = p_username and pwd = p_password;
IF retval != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSEIF role = "executive" THEN
SELECT COUNT(execid) INTO retval FROM executive WHERE execid = p_username and pwd = p_password;
IF retval != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSEIF role = "admin" THEN
SELECT COUNT(empid) INTO retval FROM employee WHERE empid = p_username and pwd = p_password;
IF retval != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;
hope this will help you...!
希望能帮到你...！
回答by Limitless isa
SELECT *,
IF(discount='0',
( IF(tax='0', discount_price, discount_price + (discount_price * (tax_rate/100))) ),
( IF(tax='0', price, price + (price * (tax_rate/100))) )
) AS price_last
FROM products WHERE id=1
