Mysql的if或case分支语法

分支测试用例表：

drop table if exists t1;
create table t1 (id int);
insert into t1 values 
(1),(2),(3),(4),(5),(6) ;

语句 中的分支

if函数：

select if(id>3,'大班','小班') from t1;

case 等值分支：

select 
CASE id
	WHEN 1 THEN
		'一班'
	WHEN 2 THEN
		'二班'
	WHEN 3 THEN
		'三班'
	WHEN 4 THEN
		'四班'
	WHEN 5 THEN
		'五班'		
	ELSE
		'没班'
END 
from t1;

case 范围分支：

select
CASE 
	WHEN id < 3 THEN
		'小班'
	WHEN id = 3 THEN
		'中班'
	WHEN id > 3 THEN
		'大班'
	ELSE
		'没班'
END
from t1;

方法 中的分支

存储过程 中case：

delimiter $
drop PROCEDURE if EXISTS p01;
create PROCEDURE p01(in i int,out o1 VARCHAR(9))
BEGIN
CASE 
	WHEN i < 3 THEN
		SELECT '小班' into o1;
	WHEN i = 3 THEN
		SELECT '中班' into o1;
	WHEN i > 3 THEN
		SELECT '大班' into o1;
	ELSE
		SELECT '没有班' into o1;
END CASE;
END $
call p01(1,@o); select @o;
call p01(2,@o); select @o;
call p01(3,@o); select @o;
call p01(4,@o); select @o;
call p01(5,@o); select @o;
call p01(6,@o); select @o;

函数 中case:

delimiter $
drop FUNCTION if EXISTS f01;
create FUNCTION	f01(i int) RETURNS VARCHAR(9)
BEGIN
declare w VARCHAR(9);
CASE
	WHEN i<3 THEN
		set w='小班';
	WHEN i=3 THEN
		set w='中班';
	WHEN i>3 THEN
		set w='大班';
	ELSE
		set w='没有班级';
END CASE;
RETURN w ;
END $
select f01(1);
select f01(2);
select f01(3);
select f01(4);
select f01(5);
select f01(6);

函数 中if

delimiter $ 
drop FUNCTION if EXISTS f02;
CREATE FUNCTION f02(i int) RETURNS varchar(9)
BEGIN
	DECLARE out1 VARCHAR(9);
	IF i<3 THEN
		set out1='小班';
	ELSEIF i=3 THEN
		set out1='中班';
	ELSEIF i>3 THEN
		set out1='大班';
	ELSE
		set out1='哈哈没有班';
	END IF;
	RETURN out1 ;
END $
select f02(1);
select f02(2);
select f02(3);
select f02(4);
select f02(5);
select f02(6);