SQL中 case when then else end的用法

基础简介

当我们需要从数据源上直接判断数据显示所代表的含义时 ，可以在SQL语句中使用 Case When 函数。

case有两种格式：简单case函数和case搜索函数。

--简单case函数

 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
--例如
case sex
     when '1' then '男'
     when '2' then '女'
     else '其他' end

--case搜索函数            

CASE WHEN [expr] THEN [result1]…ELSE [default] END
--例如
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，如写判定式。 还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

--例如，下面这段sql，永远无法得到结果“第二类”：           

case when col_1 in ( 'a', 'b') then'第一类'
     when col_1 in ('a')  then '第二类'
     else '其他' end

在Check中使用Case函数

很多情况下，在Check中使用Case函数都是非常不错的解决方法。可能有很多人经常不用Check，那么我建议你看过下面的例子之后也尝试在SQL中使用Check。下面我们看一个实例：公司A有个规定，女职员的工资必须高于1000块，使用Check和Case来体现，如下所示 

CONSTRAINT check_salary CHECK 
           ( CASE WHEN sex = '2' 
                  THEN CASE WHEN salary > 1000 
                        THEN 1 ELSE 0 END 
                  ELSE 1 END = 1 ) 
--如果单纯使用Check
CONSTRAINT check_salary CHECK 
           ( sex = '2' AND salary > 1000 ) 
--这时符合了女职员的条件，但男职员无法输入

两个表数据是否一致的检查

Case函数不同于DECODE函数，在Case函数中，可以使用BETWEEN、LIKE、IS NULL、IN、EXISTS等。如使用IN、EXISTS，可以进行子查询，从而 实现更多的功能。 
实例：有两个表 tbl_A、tbl_B，两个表中都有 keyCol 列，需要对两个表进行比较，tbl_A 中的 keyCol 列数据如果在 tbl_B 中的 keyCol 列数据中可以找到，返回结果'Matched'，如果没有找到，返回结果'Unmatched'。 要实现这个功能，可以使用下面两条语句：使用IN 和 EXISTS的结果相同，也可以使用 NOT IN 和 NOT EXISTS，但这时要注意 NULL的情况。

--使用IN时
SELECT keyCol, 
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) 
    THEN 'Matched' 
    ELSE 'Unmatched' END Label 
FROM tbl_A; 

--使用EXISTS时
SELECT keyCol, 
CASE WHEN EXISTS ( SELECT * FROM tbl_B 
     WHERE tbl_A.keyCol = tbl_B.keyCol ) 
     THEN 'Matched' 
     ELSE 'Unmatched' END Label 
FROM tbl_A; 

 在Case函数中使用合计函数

假设有一张学生表：

学号(std_id)	课程ID(class_id)	课程名(class_name)	主修flag（main_class_flg)
100	1	经济学	Y
100	2	历史学	N
200	2	历史学	N
200	3	考古学	Y
200	4	计算机	N
300	4	计算机	N
400	5	管理学	N
500	6	心理学	N

 

 

 

 

 

 

 

 

 

可以看出，有部分学生同时选择修多门课程(100,200)，也有部分学生只选择一门课程(300,400,500)。选修多门课程的学生，要选择其中一门课程作为主修，主修flag里面写入 Y；只选择一门课程的学生，主修flag为N (实际上要是写入Y的话，就没有下面的麻烦事了，为了举例子)。 
现在我们要按照下面两个条件对这个表进行查询 ：1、只选修一门课程的学生，返回该课程的ID ；2、选修多门课程的学生，返回所选的主课程ID 

最简单的方法是执行两条不同的SQL语句进行查询，代码如下：

--条件1：只选择一门课程的学生 
SELECT std_id, MAX(class_id) AS main_class 
FROM Studentclass 
GROUP BY std_id 
HAVING COUNT(*) = 1; 

--条件2：选择多门课程的学生 
SELECT std_id, class_id AS main_class 
FROM Studentclass 
WHERE main_class_flg = 'Y' ; 

如果使用Case函数，只需要一条SQL语句就可以解决两个查询问题，具体代码如下：

SELECT  std_id, 
CASE WHEN COUNT(*) = 1    --只选择一门课程的学生 
     THEN MAX(class_id) 
     ELSE MAX(CASE WHEN main_class_flg = 'Y' --选择多门课程的学生
              THEN class_id 
              ELSE NULL END 
    ) 
    END AS main_class 
FROM Studentclass 
GROUP BY std_id; 

通过在Case函数中嵌套Case函数，在Case函数中使用合计函数等方法，我们可以轻松的解决这个问题，使用Case函数给我们带来了更大的自由度。 最后提醒一下使用Case函数的新手注意不要犯下面的错误 

CASE col_1 
    WHEN 1   THEN 'Right' 
    WHEN NULL  THEN 'Wrong' 
END 

在这个语句中When Null 这一行总是返回unknown，所以永远不会出现Wrong的情况。因为这句可以替换成WHEN col_1 = NULL，这是一个错误用法，正确的使用方法是WHEN col_1 IS NULL。