SQL中的CASE的用法

CASE在SQL语句中，很有点类似java等高级编程语言中的switch这样子的多分枝语句，但是有点不同的是，case后面接的是when，另外，when的后续分枝有点类似if后面接else。这个是我的个人认识，case 。。when。。then。。[else]。。

下面，看看MySQL的帮助信息，可以看到case的基本语法结构：

topics:　　　　　　　　#CASE的两种基本用法
   CASE OPERATOR
   CASE STATEMENT

MariaDB [mysql]> help case statement;  #case的第一种用法
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

MariaDB [mysql]> help case operator;　　#case的第二种基本用法
Name: 'CASE OPERATOR'
Description:
Syntax:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END

 

这里，我还需要说明的是，case可以和select，check，update等一起配合使用，相当于增加了SQL操作的条件分析，是的SQL写的可以更加强大。

1. select的配合用法

Examples:
MariaDB> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
MariaDB> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
MariaDB> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

MariaDB [test]> select * from casetbl;
+----+------+---------+---------+--------+
| id | age  | name    | country | salary |
+----+------+---------+---------+--------+
|  1 |   33 | shihuc  | china   |   1000 |
|  2 |   34 | water   | china   |   2000 |
|  3 |   20 | taikang | america |   3000 |
|  4 |   50 | amazon  | america |   5000 |
+----+------+---------+---------+--------+
4 rows in set (0.00 sec)

MariaDB [test]> SELECT
    -> CASE WHEN salary <= 2000 THEN '1'
    -> WHEN salary > 2000 AND salary <= 4000  THEN '2'
    -> ELSE NULL END salary_class,
    -> COUNT(*)  FROM  casetbl
    -> GROUP BY
    -> CASE WHEN salary <= 2000 THEN '1'
    -> WHEN salary > 2000 AND salary <= 4000  THEN '2'
    -> ELSE NULL END;
+--------------+----------+
| salary_class | COUNT(*) |
+--------------+----------+
| NULL         |        1 |
| 1            |        2 |
| 2            |        1 |
+--------------+----------+
3 rows in set (0.00 sec)

 

2. check的配合用法

比如，某些公司对于男性员工招聘的时候有年龄的限制，年龄必须小于40岁，当数据插入到表中的时候，这个年龄就会自动被检查。

MariaDB [test]> create table casechktbl(
    -> id int primary key auto_increment,
    -> age int not null,
    -> name varchar(30),
    -> salary int (5) check (case when age > 35 then 1 else 0 end));
Query OK, 0 rows affected (0.25 sec)

MariaDB [test]> desc casechktbl;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| age    | int(11)     | NO   |     | NULL    |                |
| name   | varchar(30) | YES  |     | NULL    |                |
| salary | int(5)      | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

MariaDB [test]> insert into casechktbl (name, age, salary) values("hailang", 36, 10);
Query OK, 1 row affected (0.04 sec)

MariaDB [test]> select * from casechktbl;
+----+-----+---------+--------+
| id | age | name    | salary |
+----+-----+---------+--------+
|  1 |  36 | hailang |     10 |
+----+-----+---------+--------+
1 row in set (0.00 sec)

这里需要说明的是，这个测试是在MySQL下进行的，这个例子可能看不出什么问题，因为MySQL中的check其实是一个摆设，不起重要（当用于约束的时候）。

 

3. update配合使用

有如下更新条件

1.工资5000以上的职员，工资减少10%。 2.工资在2000到4600之间的职员，工资增加15%

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

--条件1 

UPDATE Personnel  SET salary = salary * 0.9  WHERE salary >= 5000; 

--条件2 

UPDATE Personnel  SET salary = salary * 1.15 

WHERE salary >= 2000 AND salary < 4600;

这个例子，其实很容易看出问题，因为这两个条件语句的执行是分开操作的，所以会出现抖动，就是说执行第一个条件后，他的工资变成了符合第二个条件的情况，则会再次被修改。

比如路人甲的工资是5000，执行第一个修改后，变成了4500，很显然这个水平符合第二个条件，就是要增长10%，则最终其工资为5175.他这么一调整，工资不降反增了。。。

 

所以，需要用case条件来处理。

MariaDB [test]> select * from casetbl;
+----+------+---------+---------+--------+
| id | age  | name    | country | salary |
+----+------+---------+---------+--------+
|  1 |   33 | shihuc  | china   |   1000 |
|  2 |   34 | water   | china   |   2000 |
|  3 |   20 | taikang | america |   3000 |
|  4 |   50 | amazon  | america |   5000 |
|  5 |   37 | hailang | germany |   5000 |
+----+------+---------+---------+--------+
5 rows in set (0.00 sec)


MariaDB [test]> update casetbl set salary =
    -> case when salary >= 5000 then salary * 0.9
    -> when salary >=2000 and salary <4600 then salary * 1.15
    -> else salary end;
Query OK, 4 rows affected (0.05 sec)
Rows matched: 5  Changed: 4  Warnings: 0

MariaDB [test]>
MariaDB [test]> select * from casetbl;
+----+------+---------+---------+--------+
| id | age  | name    | country | salary |
+----+------+---------+---------+--------+
|  1 |   33 | shihuc  | china   |   1000 |
|  2 |   34 | water   | china   |   2300 |
|  3 |   20 | taikang | america |   3450 |
|  4 |   50 | amazon  | america |   4500 |
|  5 |   37 | hailang | germany |   4500 |
+----+------+---------+---------+--------+
5 rows in set (0.00 sec)

从上面的结果，是不是很容易看出结果是符合逻辑的。