Case has two formats: simple case function and case search function.
– simple Case function
The function of a simple Case expression is to use the expression to determine the return value:
select id,name, case sex when '1' then 'male' when '2' then 'female' else 'other' end from student
– search Case function:
Case function (case search function): judge whether the expression is true or false. If it is true, return the result; If false, else value is returned; If the else value is not defined, a null value is returned (the return value is determined by the condition);
select id,name, CASE WHEN sex = '1' THEN 'male' WHEN sex = '2' THEN 'female' ELSE 'other' END from student
These two methods can achieve the same function. The writing method of simple Case function is relatively simple, but compared with Case search function, there are some functional limitations, such as writing judgment.
Another problem to note is that the Case function only returns the first qualified value, and the rest of the Case will be ignored automatically
Usage of CASE WHEN: once a certain WHEN is satisfied, this data will exit CASE WHEN without considering other cases;
For example, the following SQL will not get the result of the second when: (the value of 'difference' will not be obtained)
The sql is as follows
case when colum in ('a', 'b') then 'excellent'
when colum in ('a') then 'difference'
else 'other' end
Some examples of case functions
- Known data are grouped and analyzed in another way
The following data are available:
According to the country's population data, the population of Asia and North America is counted. You should get the following result.
Create table
CREATE TABLE `table_a` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`country` VARCHAR(100) DEFAULT NULL,
`population` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO table_A (country,population) VALUES('China',600);
INSERT INTO table_A (country,population) VALUES('U.S.A',100);
INSERT INTO table_A (country,population) VALUES('Canada',100);
INSERT INTO table_A (country,population) VALUES('britain',200);
INSERT INTO table_A (country,population) VALUES('France',300);
INSERT INTO table_A (country,population) VALUES('Japan',250);
INSERT INTO table_A (country,population) VALUES('Germany',200);
INSERT INTO table_A (country,population) VALUES('Mexico',50);
INSERT INTO table_A (country,population) VALUES('India',250);
use case when query
SELECT
CASE country
WHEN 'China' THEN 'Asia'
WHEN 'India' THEN 'Asia'
WHEN 'Japan' THEN 'Asia'
WHEN 'U.S.A' THEN 'North America'
WHEN 'Canada' THEN 'North America'
WHEN 'Mexico' THEN 'North America'
ELSE 'other'
END AS 'continent',
SUM(population) AS 'population'
FROM table_A
GROUP BY CASE country
WHEN 'China' THEN 'Asia'
WHEN 'India' THEN 'Asia'
WHEN 'Japan' THEN 'Asia'
WHEN 'U.S.A' THEN 'North America'
WHEN 'Canada' THEN 'North America'
WHEN 'Mexico' THEN 'North America'
ELSE 'other' END;
You should get the following result:
Similarly, we can also use this method to judge the salary level and count the number of people at each level. The SQL code is as follows
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 Table_A 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;
- Use an SQL statement to complete the grouping of different conditions
The data are as follows:
Create table
CREATE TABLE `table_a` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`country` VARCHAR(100) DEFAULT NULL,
`population` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO table_A (country,sex,population) VALUES('China',1,340);
INSERT INTO table_A (country,sex,population) VALUES('China',2,260);
INSERT INTO table_A (country,sex,population) VALUES('U.S.A',1,45);
INSERT INTO table_A (country,sex,population) VALUES('U.S.A',2,55);
INSERT INTO table_A (country,sex,population) VALUES('Canada',1,51);
INSERT INTO table_A (country,sex,population) VALUES('Canada',2,49);
INSERT INTO table_A (country,sex,population) VALUES('britain',1,40);
INSERT INTO table_A (country,sex,population) VALUES('britain',2,60);
use case when Grouping query
SELECT country AS 'country',
SUM(CASE WHEN sex=1 THEN population ELSE 0 END) AS 'male',
SUM(CASE WHEN sex=2 THEN population ELSE 0 END) AS 'female'
FROM table_A
GROUP BY country;
Grouped by country and gender, the results are as follows
Remind novices using the Case function not to make the following mistakes
CASE col_1 WHEN 1 THEN 'Right' WHEN NULL THEN 'Wrong' END
In this statement, the line When Null always returns unknown, so there will never be Wrong. Because this sentence can be replaced by WHEN col_1 = NULL, which is an incorrect usage. At this time, we should choose WHEN col_1 IS NULL.
3 . UPDATE selected according to conditions
For example, there are the following update conditions
1> . For employees with a salary of more than 5000, the salary shall be reduced by 10%
2> . For employees with wages between 2000 and 4600, their wages will increase by 15%
It is easy to consider that you can choose to execute the UPDATE statement twice, as shown below:
– condition 1
update salarys set salary=salary*0.9 where salary >= 5000;
– condition 2
update salarys set salary=salary*1.15 where salary >= 2000 and salary < 4600
But things are not as simple as imagined. Suppose there is a personal salary of 5000 yuan First, according to condition 1, the salary is reduced by 10% to 4500 Next, when running the second SQL, because the salary of this person is 4500, which is in the range of 2000 to 4600, it needs to be increased by 15%. Finally, the salary of this person is 5157, which is not reduced, but increased If it is carried out in reverse, people with a salary of 4600 will instead reduce their salary If we want an SQL statement to realize this function, we need to use the Case function The code is as follows
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;
It should be noted that the esle salary in the last line is required. Without this line, the salary of people who do not meet these two conditions will be written as NULL In the case function, the default value of else is NULL, which should be noted
reference resources:
https://blog.csdn.net/muriyue6/article/details/73442054
https://blog.csdn.net/muriyue6/article/details/73442054
https://www.jianshu.com/p/cbb05073931f
https://www.cnblogs.com/xiaowu/archive/2011/08/17/2143445.html
https://help.aliyun.com/document_detail/62764.html