对SQL语句中case when...then...else...end的理解

最近在开发中遇到一个业务想了好久写了很多代码都解决不了，最后发现一句SQL语句就能完美解决了。

是这样的，商城中各商品都有对应的市场价marketprice，存在对应的数据表里shop_goods。

根据客户的会员等级RANK_ID不同可以给予不同的优惠价USER_PRICE，在页面呈现展示，重要的是用户点击后要按照价格高低排序

，所以要在一条sql语句中执行。

而这些价格是以RANK_ID（会员等级）-GOODS_ID（商品ID）-USER_PRICE（会员售价）的逻辑关系存储在另一张表rank_price中。

以下是对应的表，和我写的SQL语句

    shop_goods                                                     rank_price

                                                                     

SELECT sg.id,sg.marketprice,(CASE WHEN rp.USER_PRICE IS NOT NULL THEN rp.USER_PRICE ELSE sg.marketprice END) AS marketprice
 FROM shop_goods AS sg LEFT JOIN rank_price as rp ON sg.id = rp.GOODS_ID AND rp.RANK_ID = 2 ORDER BY marketprice ASC;

假设用户是银卡会员（RANK_ID = 2）,取得对应的优惠价并将价格按升(或降)排序

                                                                                     

重点就在这句：

(CASE WHEN rp.USER_PRICE IS NOT NULL THEN rp.USER_PRICE ELSE sg.marketprice END) AS marketprice

用中文解释大概是这个意思，当用户会员等级对应的商品优惠价不为空即存在优惠价时（case when…is not null），就把优惠价赋值给marketprice字段中对应的值(then…as…)，如果商品没有优惠价时还是取原来的市场价（else…as…）,结束判断end。

这是我第一次遇到场景用这方法解决，当然还会有其他用法，很多大神也都有发掘，只是想在这里记录下自己的理解过程。

SQL利用Case When Then多条件判断

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    WHEN 条件4 THEN 结果4
.........
    WHEN 条件N THEN 结果N
    ELSE 结果X
END

Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

例如：

SELECT     id, name, cj, (CASE WHEN cj < 60 THEN '不及格' WHEN cj BETWEEN 60 AND 90 THEN '良好' WHEN cj > 90 THEN '优秀' END) AS 状态
FROM   stud

 

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
--比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
         WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END