Case when 的用法: 一旦满足了某一个WHEN, 则这一条数据就会退出CASE WHEN , 而不再考虑 其他CASE;

Case when 的用法

--   搜索Case函数:

Case函数(Case搜索函数): 判断表达式的真假,如果为真,返回结果;如果为假,返回else值;如果未定义else值,则返回空值(使用条件确定返回值);

select name,id,(case when id=34 then salary*2

                                    when id=45 then salary*3

                                    else salary

                                    end) new_salary

from semp;

--  简单Case函数

简单Case表达式的作用是: 使用表达式确定返回值:

select id,name,(case sex

       when '1' then '男'

       when '2' then '女'

        else '其他' end)

from student;

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

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

比如下面 SQL,将无法得到第二个结果:(差永远获取不到)

sql如下

case when colum in ('a', 'b') then '优秀'

         when colum in ('a') then '差'

          else '其他' end 

 

下面我们来看一下,使用Case函数都能做些什么事情 .

1. 已知数据按照另一种方式进行分组 ,分析

有如下数据:(为了看的更清楚 ,我并没有使用国家代码, 而是直接用国家名作为 Primary Key)

 

 

同样我们也可以用这个方法来判断工资等级, 并统计出来每一等级的人数. SQL如下

 

SELECT 
CASE WHEN salary <= 500 THEN '1' 
WHEN salary > 500 AND salary <= 600  THEN '2' 
WHEN salary > 600 AND salary <= 800  THEN '3' 
WHEN salary > 800 AND salary <= 1000 THEN '4' 
ELSE NULL END salary_class, -- 别名命名
COUNT(*) FROM salary 
GROUP BY 
CASE WHEN salary <= 500 THEN '1' 
WHEN salary > 500 AND salary <= 600  THEN '2' 
WHEN salary > 600 AND salary <= 800  THEN '3' 
WHEN salary > 800 AND salary <= 1000 THEN '4' 
ELSE NULL END; 

 

2. 用一个SQL语句完成不同条件的分组 .

    有如下数据:

    按照国家和性别进行分组 , 得出结果如下:

    代码

SELECT country,
SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) as '男', -- 男性人口 
SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) as '女'-- 女性人口
FROM citys GROUP BY country; 

 

3. 在Check中使用Case函数 .

SQL 中 Check 约束用于限制列中的值得范围 .

如果对单个列定义Check约束 , 那么该列只允许特定的值 .

如果对一个表定义Check约束 ,  那么此约束会基于行中其他列的值在特定的列中对值进行限制 .

Create Table 时 的 SQL Check 约束 :

下面的 SQL  在 " Persons" 表创建时在 "Ｐ_Id＂列上创建 Check 约束 . Check 约束规定 " P_Id" 列必须只包含大于0的整数 .

CREATE TABLE Persons

( 

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CHECK (P_Id>0)

)

如果需要命名Check 约束 , 并定义多个列的Check 约束 , 请使用下面的SQL语法 : 

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT  che_Person Check (P_Id>0 AND City='Sandnes')

)

 

Alter Table 时 的 SQL Check 约束:

当表已被创建时, 如果在 "P_Id" 列创建 Check 约束 , 请使用下面的SQL :

ALTER TABLE Persons

ADD CHECK (P_Id>0)

如果需要命名 CHECK 约束, 并定义多个列的 CHECK 约束 , 请使用下面的SQL 语法 :

ALTER TABLE Persons

ADD CONSTRAINT  che_Person Check (P_Id>0 AND City='Sandnes')

 

撤销 Check 约束

如果需要撤销 Check 约束 , 请使用下面的SQL :

ALTER TABLE Persons

DROP Check che_Pesrson

 

4 . 根据条件有选择的UPDATE .

例 : 有如下跟新条件

1> . 工资5000以上的职工 , 工资减少10%

2> . 工资在2000到4600之间的职工 , 工资增加15%

很容易考虑的是选择执行两次UPDATE语句 , 如下所示 :

--  条件1

update salarys  set salary=salary*0.9 where salary >= 5000;

--  条件 2

update salarys set salary=salary*1.15 where salary >= 2000 and salary < 4600

但是事情没有想象得那么简单 , 假设有个人工资 5000 块 . 首先按照条件 1 , 工资减少 10 % , 变成工资4500 . 接下来运行第二个sql的时候 ,  因为这个人的工资是4500 在2000到4600 的范围内 , 需增加15% , 最后这个人的工资结果是5157 , 不但没减少, 反而增加了 . 如果要是 反过来执行, 那么工资4600的人相反会变成减少工资 . 如果要一个SQL 语句实现这个功能的话 , 我们需要用到Case函数 . 代码如下

update salarys set salary = 

case when salary >= 5000 then salary*0.9

         when salary >= 2000 and salary < 4600 then salary*1.15

else salary end;

这里要注意一点 , 最后一行的 esle salary 是必须的 , 要是没有这行, 不符合这两个条件的人的工资将会被写成NULL . 在case 函数中else部分的默认值为NULL , 这点是需要注意的地方 .