SQL之case when then用法详解

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</span>
    
    
   
   
 
 

这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。

还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

 
 

   
   

    
    

   
   
   
   

    
    

     
     --比如说，下面这段sql，你永远无法得到“第二类”这个结果
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     case when col_1 in ('a','b') then '第一类'
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
          when col_1 in ('a') then '第二类'
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
          else '其他' 
     
     end
    
    
   
   
 
 

实例演示：
首先创建一张users表，其中包含id,name,sex三个字段，表内容如下：

select * from users
 
 

 

 
 

   
   

    
    

   
   
   
   

    
    

     
      ID        NAME                 SEX
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ---------- -------------------- ----------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     1          张一                 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     2          张二                 1
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     3         张三                 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     4          张四                
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     5          张五                 2
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     6          张六                 1
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     7          张七                 2
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     8          张八                 1
    
    
   
   
 
 

1、上表结果中的"sex"是用代码表示的，希望将代码用中文表示。可在语句中使用case语句：

 
 

   
   

    
    

   
   
   
   

    
    

     
       1 
     
     select u.id,u.name,u.sex,
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     2    (
     
     case u.sex
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     3      
     
     when 
     
     1 
     
     then 
     
     '男'
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     4      
     
     when 
     
     2 
     
     then 
     
     '女'
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     5      
     
     else 
     
     '空的'
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     6      
     
     end
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     7     )性别
    
    
   
   

   
   

    
    

   
   
   
   

    
    
  
     
     8  
     
     from 
     
     users u;
    
    
   
   
 
 

 

 
 

   
   

    
    

   
   
   
   

    
    

     
                                          ID NAME                        SEX 性别
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --------------------------------------- -------------------- ---------- ------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           1 张一                            空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           2 张二                          1 男
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           3 张三                            空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           4 张四                            空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           5 张五                          2 女
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           6 张六                          1 男
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           7 张七                          2 女
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           8 张八                          1 男
    
    
   
   
 
 

2、如果不希望列表中出现"sex"列，语句如下：

 
 

   
   

    
    

   
   
   
   

    
    

     
     select u.id,u.name,
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       2    (case u.sex
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       3      when 1 then '男'
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       4      when 2 then '女'
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       5      else '空的'
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       6      end
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       7     )性别
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       8  from users u;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                          ID NAME                 性别
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --------------------------------------- -------------------- ------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           1 张一                 空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           2 张二                 男
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           3 张三                 空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           4 张四                 空的
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           5 张五                 女
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           6 张六                 男
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           7 张七                 女
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
                                           8 张八                 男
    
    
   
   
 
 

3、将sum与case结合使用，可以实现分段统计。
     如果现在希望将上表中各种性别的人数进行统计，sql语句如下：

 
 

   
   

    
    

   
   
   
   

    
    

     
     select
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       2    sum(case u.sex when 1 then 1 else 0 end)男性,
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       3    sum(case u.sex when 2 then 1 else 0 end)女性,
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       4    sum(case when u.sex 
     
     <>1 and u.sex
     
     <>2 then 1 else 0 end)性别为空
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       5  from users u;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
             男性         女性       性别为空
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ---------- ---------- ----------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              3          2          0
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     --------------------------------------------------------------------------------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     SQL> select
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       2    count(case when u.sex=1 then 1 end)男性,
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       3    count(case when u.sex=2 then 1 end)女,
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       4    count(case when u.sex 
     
     <>1 and u.sex
     
     <>2 then 1 end)性别为空
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
       5  from users u;
    
    
   
   

   
   

    
    

   
   
   
   

    
    
 
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
             男性          女       性别为空
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ---------- ---------- ----------
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
              3          2          0
    
    
   
   
 
 

 

转自：https://blog.youkuaiyun.com/wangzl1163/article/details/51423326