SQL总复习二：CASE表达式的妙用

SQL中的case表达式的作用是用来对“某个变量”进行某种转化，通常在select字句中使用

基本用法

举个例子：

​​

不难看出，​

​case​

​表达式很像我们的 ​

​if else ​

​的作用，在发现为真的 ​

​WHEN​

​ 子句时，​

​CASE​

​ 表达式的真假值判断就会中止，而剩余的 ​

​WHEN​

​ 子句会被忽略。​

​case​

​表达式有两种写法：

CASE sex
  WHEN '1' THEN '男'
  WHEN '2' THEN '女'
ELSE '其他' END
      

CASE WHEN sex = '1' THEN '男'
   WHEN sex = '2' THEN '女'
ELSE '其他' END
      

• ​

  ​else​

  ​这句非必须但最好带上，如果​

  ​when​

  ​中没有符合条件的，且有没有​

  ​else​

  ​控制，那么，这样的case会返回null，null不是个好东西，所以你一定要尽量控制减少返回null的情况。
• 简单点说，在能写列名和常量的地方，通常都可以写 ​

  ​CASE​

  ​ 表达式，因为它返回的是一个标量值。

使用case实现不同条件的统计

有如下一个表：

上图中，下半部分查询结果如何得到呢？即如果让你查询出各个省份的男生总数和女生总数你会怎么搞呢？

看看如何用case实现的：

--查看所有学生信息
select StudentID,[Name],Province,Gender,(case Gender
                                    when 0 then N'女'
                                    when 1 then N'男'
                                    else '' end) StuGender
from dbo.T_Student

--查询出各省的男女生总数：
select Province,
sum(case when Gender=1 then 1 else 0 end ) as MaleCount,
sum(case when Gender=0 then 1 else 0 end ) as FeMaleCount
from dbo.T_Student
group by Province
      

在update操作中使用case条件分支，对不同范围的数据执行更新

比如有个员工薪资表Salaries，工资字段为salary ，

假设现在需要根据以下条件对该表的数据进行更新。

1. 对当前工资为 30 万日元以上的员工，降薪 10%。
2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

那么更新的SQL语句可以这样写：

-- 用CASE 表达式写分段的更新操作
UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salay END; 
      

生成每月数据统计报表

有以下两张表：

那么SQL语句可以这样写：

-- 表的匹配：使用IN 谓词
SELECT course_name,
       CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200706) THEN '○'
            ELSE '×' END AS "6 月",
       CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200707) THEN '○'
            ELSE '×' END AS "7 月",
       CASE WHEN course_id IN
(SELECT course_id FROM OpenCourses
WHERE month = 200708) THEN '○'
            ELSE '×' END AS "8 月"
FROM CourseMaster;

-- 表的匹配：使用EXISTS 谓词
SELECT CM.course_name,
       CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200706
AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END AS "6 月",
       CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200707
AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END AS "7 月",
       CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
WHERE month = 200708
AND OC.course_id = CM.course_id) THEN '○'
            ELSE '×' END  AS "8 月"
FROM CourseMaster CM;
      

case与行转换成列

SELECT name,
 CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "SQL 入门",
 CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "UNIX 基础",
 CASE WHEN SUM(CASE WHEN course = 'Java 中级' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "Java 中级 "
FROM Courses
GROUP BY name;
      

case与null

当case使用的变量或列的值可能为null时，唯一正确的使用方式如下：

CASE 
  WHEN col_1 = 1 THEN '○'
  WHEN col_1 IS NULL THEN '×'
END
--而不是：
CASE col_1
 WHEN 1 THEN '○'
 WHEN NULL THEN '×'
END

      

参考书籍：图灵社区的《SQL进价教程》