mysql - 如何在 mySQL 的 Case 表达式中放置 IF 语句？

Question

我正在做一道家庭作业题，我必须在 case 语句中放置一个 IF 语句。我在网上找不到任何解释如何执行此操作的内容。我想我的语法错误。代码如下:

/*Set DB context and drop the procedure if it exists (2 lines)*/
use ap;
drop procedure if exists ch13_5;


/*Delimiter statement (1 line)*/
delimiter //

/*Create procedure statement (1 line).*/
create procedure ch13_5()

/*Begin statement (1 line)*/
begin

/*Declare 3 internal variables; 2 varchars's and 1 int (3 lines)*/
declare v_state varchar(2);
declare v_city varchar(30);
declare inv_id int;



/*Set the int variable as directed in the assignment (1 line)*/
set inv_id = 15;

/*Set the internal varchar variables using a select column_value, column_value into */
/*the appropriate variables using the where condition of invoice_id = int variable (5 lines) */
select vendor_state, vendor_city into v_state, v_city
from invoices where invoice_id = inv_id;





/*BEGIN CASE and IF-ELSEIF-ELSE CONDITIONAL*/
/*Start a CASE statement using the state variable- when it's AZ, display "Arizona vendor' (3 lines) */
case
when v_state ='AZ' then 
select 'Arizona vendor' AS v_state;
when v_state='CA' then if
v_city = 'Fresno' then select 'Fresno California vendor' as v_city;
elseif v_city = 'Oxnard' then select 'LA Metro California vendor' as v_city;
else select 'California vendor' as v_city;
SELECT 'California vendor' as v_state;
else 
select 'National vendor' AS v_state;
end case;
end if;

提前感谢您的帮助:)

Answer 1

END IF 需要位于包含 IF 语句的 WHEN block 内。

CASE
WHEN v_state = 'AZ'
THEN SELECT 'Arizona vendor' AS v_state;
WHEN v_state = 'CA'
THEN IF v_city = 'Fresno'
     THEN SELECT 'Fresno California vendor' AS v_city;
     ELSEIF v_city = 'Oxnard'
     THEN SELECT 'LA Metro Califonia vendor' AS v_city;
     ELSE SELECT 'California vendor' AS v_city;
     END IF;
ELSE SELECT 'National vendor' AS v_state;
END CASE;