第11课：创建新的输出字段（2）

本节要点

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分，其他用户不奖励。请计算每个用户应该奖励的积分数。

picture loss