1、if
IF(expr1,expr2,expr3)
流程控制,可以理解为case when then else end
的语法糖,或者理解为三目运算符
例如:
# 1 可以放在select中,字段转换输出
select emp_no, emp_name,
if(emp_type=10,'合同工','临时工') emp_type_name
from hr_employee
# 2 可以放在where条件中,动态条件
SELECT a.co_id, a.goods_id, a.balance_count
from wm_inventory a
where a.balance_count <> 0
and if(in_coid='',1,a.co_id = in_coid)
GROUP BY a.co_id, a.goods_id;
2、case when then
# 1 CASE score WHEN 'A' THEN '优' WHEN 'B' THEN '良' WHEN 'C' THEN '中' ELSE '不及格' END
CASE value WHEN [compare_value] THEN result
[WHEN [compare_value] THEN result ...]
[ELSE result]
END
# 2 CASE WHEN SCORE = 'A' THEN '优' WHEN SCORE = 'B' THEN '良' WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
# condition是一个返回布尔类型的表达式
CASE WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END
流程控制,THEN后边的值与ELSE后边的值类型应一致,否则会报错。如下:
CASE SCORE WHEN 'A' THEN '优' ELSE 0 END
'优’和0数据类型不一致则报错: [Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
使用场景
-
切换数据来源
用户留的联系方式,可能为QQ、电话、邮箱。如果QQ获取QQ列数据,如果电话获取phone数据,如果邮箱获取email数据
# 根据所留联系方式类型不同,从 不同的列 获取不同联系方式的值,展示在一列中
SELECT CASE type WHEN 'QQ' THEN QQ
WHEN '电话' THEN phone
WHEN '邮箱' THEN email
END
- 0 、 1计数
# 计算总数或者计算多条件数量
SELECT
SUM (CASE WHEN sex = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
SUM (CASE WHEN sex = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
SUM (CASE WHEN score >= 60 AND sex = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
SUM (CASE WHEN score >= 60 AND sex = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM students
# 实现sql
SELECT `姓名`, MAX(CASE `课程` WHEN '语文' THEN `分数` ELSE 0 END) '语文',
MAX(CASE `课程` WHEN '数学' THEN `分数` ELSE 0 END) '数学',
MAX(CASE `课程` WHEN '物理' THEN `分数` ELSE 0 END) '物理'
FROM TB GROUP BY `姓名`;
分析:先分解下SQL,执行MAX内CASE WHEN 及未GROUP BY 姓名
时SQL:
# 解析sql,分步去掉max函数 去掉group by分组
SELECT `姓名`, CASE `课程` WHEN '语文' THEN `分数` ELSE 0 END '语文',
CASE `课程` WHEN '数学' THEN `分数` ELSE 0 END '数学',
CASE `课程` WHEN '物理' THEN `分数` ELSE 0 END '物理'
FROM TB;
执行group by 姓名
时因为没有任何聚合函数, 那么会从基表里取group by后字段的第一条数据。即:
因此,需要 分组 之后,要用聚合函数操作取不是0的最大值。