mysql - 如何在 mySQL 中的 Case 表达式中放置 IF 语句?
问题描述
我正在处理一个家庭作业问题,我必须在案例语句中放置一个 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;
预先感谢您的任何帮助 :)
解决方案
END IF
需要在WHEN
包含该IF
语句的块内。
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;
推荐阅读
- java - 抛出 BadRequestException(ResponseEntity) vs Catch Error,返回 ResponseEntity(HTTPStatus.BadRequest) ReST API
- php - 根据查询值有条件地格式化字体颜色
- swift - 填充 CA 层的负空间
- python - 如何创建一个函数,允许我将 .txt 文件的所有路径存储在列表中?
- node.js - 使用 node.js 中的 knex 导出数据库表模式并将数据移动到另一台机器
- python - 单击时切换单元格的颜色(Tkinter-Python)
- reactjs - React - 未捕获的类型错误:无法读取未定义的属性“组件”
- c++ - 为什么默认的复制构造函数会忽略派生类的虚方法覆盖?
- angular - Angular:HttpRequest.clone() - params 和 setParams 有什么区别?
- java - 如何在没有 JSP 页面的情况下将参数发送到 Servlet