本节要点
CASE WHEN逻辑转换
CASE WHEN逻辑转换
回到上节课提出的问题:如何根据买入数量,区分大单、中单、小单?
比如按下表的交易数量来划分:
这时,就需要使用SQL语言中的条件分支功能。
一般来说,使用CASE WHEN关键字。
SELECT volume, CASE WHEN volume >= 3000 THEN '大单' WHEN volume > 300 AND volume < 3000 THEN '中单' WHEN volume <= 300 THEN '小单' END FROM t_stock_trans_dtl WHERE opt_typ = '买入';
上面SQL中的最后一个WHEN分支,可以使用ELSE分支替换,表示其他任何情况,都走这一个分支。
SELECT volume, CASE WHEN volume >= 3000 THEN '大单' WHEN volume > 300 AND volume < 3000 THEN '中单' ELSE '小单' END FROM t_stock_trans_dtl WHERE opt_typ = '买入';
对于CASE WHEN条件分支功能,有两种语法规则。
语法一:
CASE WHEN 条件表达式1 THEN 结果表达式1
WHEN 条件表达式2 THEN 结果表达式2
…
WHEN 条件表达式N THEN 结果表达式N
[ELSE ELSE结果表达式]
END
这里的ELSE分支可以省略。当省略ELSE分支,且前面的WHEN条件没有一个满足时,CASE WHEN表达式返回NULL值。
所以,建议每一个CASE WHEN都应该有ELSE分支!!!
(1)、对于买入的交易,交易数量为正数,而卖出的交易,交易数量为负数。这就导致交易金额(交易价格*交易数量)的返回值有正有负。如何根据交易类型做转换,交易金额全部返回正数?
SELECT opt_typ,price,volume, CASE WHEN opt_typ = '买入' THEN price*volume WHEN opt_typ = '卖出' THEN 0-price*volume ELSE 0 END FROM t_stock_trans_dtl;
其实,上面这个SQL,也可以写成这样:
SELECT opt_typ,price,volume, CASE opt_typ WHEN '买入' THEN price*volume WHEN '卖出' THEN 0-price*volume ELSE 0 END FROM t_stock_trans_dtl;
这就是CASE WHEN条件分支的第二种语法:
CASE 条件表达式
WHEN 匹配表达式1 THEN 结果表达式1
WHEN 匹配表达式2 THEN 结果表达式2
…
WHEN匹配表达式N THEN 结果表达式N
[ELSE ELSE结果表达式]
END
这种写法,适用于条件表达式的结果,是一个可穷举的列表,而且列表的中的元素个数不是太多的情况下。但不适用于条件表达式的结果是一个范围的情况。
上面两种CASE WHEN表达式,在执行的时候,都是按顺序执行条件判断,一旦上面的条件满足,则不再执行下面的条件判断。
另外,我们还可以在CASE WHEN表达式内,嵌套另一个CASE WHEN表达式。比如像下面这样:
CASE WHEN 外层条件表达式1 THEN (CASE WHEN 内层条件表达式1 THEN 内层结果表达式1
…
WHEN 内层条件表达式M THEN 内层结果表达式M
ELSE 内层ELSE结果表达式
END)
…
WHEN 外层条件表达式N THEN 外层结果表达式N
ELSE 外层ELSE结果表达式
END
上面讲的两种语法格式,都可以多层嵌套,但不推荐嵌套太多层,因为嵌套太多层的话,代码的可读性会非常糟糕。建议最多嵌套两层并使用括号格式化SQL语句。
比如,下面这个CASE WHEN表达式的返回结果是?
课后习题:
1、对于第6课课后习题中的论坛注册用户表,请编写SQL完成以下查询。
(1)、查询所有用户的登录次数。但对于使用QQ邮箱进行注册的用户,登录次数+10次;使用GMAIL邮箱注册的用户,登录次数*2,其他邮箱注册的用户,登录次数原样显示。
(2)、论坛准备进行一次积分奖励活动,对于最近30天有登录的用户,奖励1000分;最近90天有登录的用户,奖励500分,最近180天有登录的用户,奖励200分,最近365天有登录的用户,奖励100分,其他用户不奖励。请计算每个用户应该奖励的积分数。