SQL中的CASE的用法

topics:　　　　　　　　#CASE的两种基本用法 2 CASE OPERATOR 3 CASE STATEMENT 4 5 MariaDB [mysql]> help case statement; #case的第一种用法 6 Name: ‘CASE STATEMENT‘ 7 Description: 8 Syntax: 9 CASE case_value 10 WHEN when_value THEN statement_list 11 [WHEN when_value THEN statement_list] ... 12 [ELSE statement_list] 13 END CASE 14 15 Or: 16 17 CASE 18 WHEN search_condition THEN statement_list 19 [WHEN search_condition THEN statement_list] ... 20 [ELSE statement_list] 21 END CASE 22 23 MariaDB [mysql]> help case operator;　　#case的第二种基本用法 24 Name: ‘CASE OPERATOR‘ 25 Description: 26 Syntax: 27 CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN 28 result ...] [ELSE result] END 29 30 CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] 31 [ELSE result] END

 

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

1. select的配合用法

1 Examples:
2 MariaDB> SELECT CASE 1 WHEN 1 THEN ‘one‘
3     ->     WHEN 2 THEN ‘two‘ ELSE ‘more‘ END;
4         -> ‘one‘
5 MariaDB> SELECT CASE WHEN 1>0 THEN ‘true‘ ELSE ‘false‘ END;
6         -> ‘true‘
7 MariaDB> SELECT CASE BINARY ‘B‘
8     ->     WHEN ‘a‘ THEN 1 WHEN ‘b‘ THEN 2 END;
9         -> NULL

 1 MariaDB [test]> select * from casetbl;
 2 +----+------+---------+---------+--------+
 3 | id | age  | name    | country | salary |
 4 +----+------+---------+---------+--------+
 5 |  1 |   33 | shihuc  | china   |   1000 |
 6 |  2 |   34 | water   | china   |   2000 |
 7 |  3 |   20 | taikang | america |   3000 |
 8 |  4 |   50 | amazon  | america |   5000 |
 9 +----+------+---------+---------+--------+
10 4 rows in set (0.00 sec)
11 
12 MariaDB [test]> SELECT
13     -> CASE WHEN salary <= 2000 THEN ‘1‘
14     -> WHEN salary > 2000 AND salary <= 4000  THEN ‘2‘
15     -> ELSE NULL END salary_class,
16     -> COUNT(*)  FROM  casetbl
17     -> GROUP BY
18     -> CASE WHEN salary <= 2000 THEN ‘1‘
19     -> WHEN salary > 2000 AND salary <= 4000  THEN ‘2‘
20     -> ELSE NULL END;
21 +--------------+----------+
22 | salary_class | COUNT(*) |
23 +--------------+----------+
24 | NULL         |        1 |
25 | 1            |        2 |
26 | 2            |        1 |
27 +--------------+----------+
28 3 rows in set (0.00 sec)

 

2. check的配合用法

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

 1 MariaDB [test]> create table casechktbl(
 2     -> id int primary key auto_increment,
 3     -> age int not null,
 4     -> name varchar(30),
 5     -> salary int (5) check (case when age > 35 then 1 else 0 end));
 6 Query OK, 0 rows affected (0.25 sec)
 7 
 8 MariaDB [test]> desc casechktbl;
 9 +--------+-------------+------+-----+---------+----------------+
10 | Field  | Type        | Null | Key | Default | Extra          |
11 +--------+-------------+------+-----+---------+----------------+
12 | id     | int(11)     | NO   | PRI | NULL    | auto_increment |
13 | age    | int(11)     | NO   |     | NULL    |                |
14 | name   | varchar(30) | YES  |     | NULL    |                |
15 | salary | int(5)      | YES  |     | NULL    |                |
16 +--------+-------------+------+-----+---------+----------------+
17 4 rows in set (0.03 sec)
18 
19 MariaDB [test]> insert into casechktbl (name, age, salary) values("hailang", 36, 10);
20 Query OK, 1 row affected (0.04 sec)
21 
22 MariaDB [test]> select * from casechktbl;
23 +----+-----+---------+--------+
24 | id | age | name    | salary |
25 +----+-----+---------+--------+
26 |  1 |  36 | hailang |     10 |
27 +----+-----+---------+--------+
28 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条件来处理。

 1 MariaDB [test]> select * from casetbl;
 2 +----+------+---------+---------+--------+
 3 | id | age  | name    | country | salary |
 4 +----+------+---------+---------+--------+
 5 |  1 |   33 | shihuc  | china   |   1000 |
 6 |  2 |   34 | water   | china   |   2000 |
 7 |  3 |   20 | taikang | america |   3000 |
 8 |  4 |   50 | amazon  | america |   5000 |
 9 |  5 |   37 | hailang | germany |   5000 |
10 +----+------+---------+---------+--------+
11 5 rows in set (0.00 sec)
12 
13 
14 MariaDB [test]> update casetbl set salary =
15     -> case when salary >= 5000 then salary * 0.9
16     -> when salary >=2000 and salary <4600 then salary * 1.15
17     -> else salary end;
18 Query OK, 4 rows affected (0.05 sec)
19 Rows matched: 5  Changed: 4  Warnings: 0
20 
21 MariaDB [test]>
22 MariaDB [test]> select * from casetbl;
23 +----+------+---------+---------+--------+
24 | id | age  | name    | country | salary |
25 +----+------+---------+---------+--------+
26 |  1 |   33 | shihuc  | china   |   1000 |
27 |  2 |   34 | water   | china   |   2300 |
28 |  3 |   20 | taikang | america |   3450 |
29 |  4 |   50 | amazon  | america |   4500 |
30 |  5 |   37 | hailang | germany |   4500 |
31 +----+------+---------+---------+--------+
32 5 rows in set (0.00 sec)

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

 

SQL中的CASE的用法

标签：