目录
case when判断语句
用法一,switch case的效果
case 字段或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
else 要显示的值n或者语句n;
end
用法二,多个if
case
when 条件1 then 要显示的值或者语句
when 条件2 then 要显示的值或者语句
else 要显示的值或者语句
end
可以不写else但是一定要有end ,没有else如果不满足条件会以null填充
SELECT
CASE
WHEN au.author_level >= 5 THEN '5-6级'
WHEN au.author_level >= 3 THEN '3-4级'
ELSE '1-2级'
END AS level_cut
FROM
author_tb au
如果else不写,当 author_level=null 时,最后该条结果会为 null
如果else存在,当 author_level =null 时,最后该条结果会为'1-2级'
IF判断语句
用法一,用在select查询中,基本语法:IF(条件,为真结果,为假结果)
SELECT
IF (au.author_level >= 5,'5-6级',
IF(au.author_level >= 3, '3-4级','1-2级')) AS level_cut
FROM
author_tb au
CASE WHEN 例子等价
用法二,复杂语句块中,(函数/存储过程/触发器)
IF condition1 THEN
{...statements to execute when condition1 is TRUE...}
[ ELSEIF condition2 THEN
{...statements to execute when condition2 is TRUE...} ]
[ ELSE
{...statements to execute when both condition1 and condition2 are FALSE...} ]
END IF;