In MySQL, case, then else end usage

Conditional statements

delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
   
   declare i int default 0;
   if i = 1 THEN
       SELECT 1;
   ELSEIF i = 2 THEN
       SELECT 2;
   ELSE
       SELECT 7;
   END IF;

END\\
delimiter ;

Looping statements

While loop

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
   DECLARE num INT ;
   SET num = 0 ;
   WHILE num < 10 DO
       SELECT
           num ;
       SET num = num + 1 ;
   END WHILE ;
END\\
delimiter ;

Repeat cycle

复制代码
delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN
   DECLARE i INT ;
   SET i = 0 ;
   repeat
       select i;
       set i = i + 1;
       until i >= 5
   end repeat;
END\\
delimiter ;

Loop

复制代码
BEGIN
   declare i int default 0;
   loop_label: loop
       
       set i=i+1;
       if i<8 then
           iterate loop_label;
       end if;
       if i>=10 then
           leave loop_label;
       end if;
       select i;
   end loop loop_label;
END

DISTINCT Removing duplicates

Acting on a single row

Select distinct name from A

Acting on multiple columns

Select DISTINCT name, ID from A

Count statistics

Select COUNT (distinct name) from A; --Number of name to go back in table

Distinct must be placed at the beginning

Select ID, distinct name from A; --The error will be indicated because the distinct must be placed at the beginning

Execute SQL statements Dynamically

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
   declare p1 int;
   set p1 = 11;
   set @p1 = p1;
   PREPARE prod FROM ‘select * from tb2 where nid > ?‘;
   EXECUTE prod USING @p1;
   DEALLOCATE prepare prod; 
END\\
delimiter ;

Case in SQL and then else end usage

Case has two formats. Simple case function and case search function.

--简单Case函数
CASE sex
        WHEN ‘1‘ THEN ‘男‘
        WHEN ‘2‘ THEN ‘女‘
ELSE ‘其他‘ END
--Case搜索函数
CASE WHEN sex = ‘1‘ THEN ‘男‘
   WHEN sex = ‘2‘ THEN ‘女‘
ELSE ‘其他‘ END

In both of these ways, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function.
There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored.

--for example, the following SQL, you can never get the result of "type two"

CASE WHEN col_1 IN ( ‘a‘, ‘b‘) THEN ‘第一类‘
        WHEN col_1 IN (‘a‘)       THEN ‘第二类‘
ELSE‘其他‘ END

Let's take a look at what you can do with the case function.

One, the known data in a different way to group, analysis.

There is the following data: (in order to see more clearly, I did not use the country code, but directly with the country name as primary Key)

Country (country)	Population (population)
China	600
United States	100
Canada	100
United Kingdom	200
France	300
Japan	250
Germany	200
Mexico	50
India	250

According to the population data of this country, the population of Asia and North America is counted. The following result should be obtained.

Chau	Population
Asia	1100
North america	250
Other	700

What would you do to solve this problem? Creating a view with a continent code is a workaround, but it is difficult to dynamically change the way statistics are used.

If you use the case function, the SQL code is as follows:

SELECT SUM (population),
Case Country
When ' China ' then ' Asia '
When ' India ' Then ' Asia '
When ' Japan ' then ' Asia '
When ' America ' Then ' North America '
When ' Canada ' then ' North America '
When ' Mexico ' then ' North America '
Else ' other ' END
From Table_a
GROUP by Case Country
When ' China ' then ' Asia '
When ' India ' Then ' Asia '
When ' Japan ' then ' Asia '
When ' America ' Then ' North America '
When ' Canada ' then ' North America '
When ' Mexico ' then ' North America '
Else ' other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows:

SELECT
Case when salary <= ' 1 '
When salary > Salary <= 2 '
When salary > Salary <= 3 '
When salary > Salary <= "4"
ELSE NULL END Salary_class,
COUNT (*)
From Table_a
GROUP by
Case when salary <= ' 1 '
When salary > Salary <= 2 '
When salary > Salary <= 3 '
When salary > Salary <= "4"
ELSE NULL END;

Two, a SQL statement to complete the different conditions of the grouping.

Have the following data

Country (country)	Gender (sex)	Population (population)
China	1	340
China	2	260
US	1	45
US	2	55
Canada	1	51
Canada	2	49
UK	1	40
UK	2	60

Grouped according to country and gender, the results are as follows

Countries	Man	Woman
China	340	260
United States	45	55
Canada	51	49
United Kingdom	40	60

In general, a Union can also be used to implement a query with a single statement. But that increases the consumption (two select parts), and the SQL statement is longer.
Here is an example of using the case function to accomplish this function

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

Third, use the case function in check.

Using the case function in check is a very good workaround in many cases. There may be a lot of people who don't have check at all, so I suggest you try using check in SQL after reading the following example.
Let's give an example.

Company A, the company has a rule that female employees must pay more than 1000 yuan. If you use check and case to behave as follows

CONSTRAINT check_salary CHECK
          ( CASE WHEN sex = ‘2‘
                 THEN CASE WHEN salary > 1000
                       THEN 1 ELSE 0 END
                 ELSE 1 END = 1 )

If you simply use check, as shown below

CONSTRAINT check_salary CHECK
          ( sex = ‘2‘ AND salary > 1000 )

The condition of the female clerk was met, and the male clerk could not enter it.

Four, according to the conditions have selected update.

example, there are the following update conditions

Employees with a salary of more than 5000 are reduced by 10%

Salary increased by 15% for employees between 2000 and 4600

It is easy to consider the option to execute two UPDATE statements as follows

--条件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;

But it's not as simple as it is supposed to be, assuming a personal salary of 5000 bucks. First of all, according to condition 1, wages are reduced by 10% to 4500 of wages. Next run the second SQL, because this person's salary is 4500 in the range of 2000 to 4600, need to increase 15%, and finally this person's salary result is 5175, not only not reduced, but increased. If the reverse is done, then the wage of 4600 will turn into a wage reduction. No matter how absurd the rules are, if you want an SQL statement to implement this function, we need to use the case function. The code is as follows:

UPDATE Personnel
SET salary = CASE WHEN salary >= 5000
　            THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600
THEN salary * 1.15
ELSE salary END;

It is important to note here that the last line of else salary is required, and if there is no such line, the wages of those who do not meet these two conditions will be written as NULL, and that will be a big bad thing. The default value of the else part in the case function is null, which is something to be aware of. This method can also be used in many places, such as changing the primary key dirty.

In general, to the two data primary key,a and B exchange, need to be temporarily stored, copied, read back the data of the three processes, if you use the case function, everything becomes much simpler.

P_key	Col_1	Col_2
A	1	Tom
B	2	John doe
C	3	Harry

Assuming that there is data, you need to exchange the primary key A and B with each other. When implemented with the case function, the code

Under

UPDATE SomeTable
SET p_key = CASE WHEN p_key = ‘a‘
THEN ‘b‘
WHEN p_key = ‘b‘
THEN ‘a‘
ELSE p_key END
WHERE p_key IN (‘a‘, ‘b‘);

The same can be exchanged for two unique keys. It should be noted that if there is a need to exchange the primary key occurs, most of the original design of the table is not in place, it is recommended to check the design of the table is appropriate.

Five, two table data is checked for consistency.

The case function differs from the DECODE function. In the case function, you can use Between,like,is null,in,exists and so on. For example, using in,exists, you can make subqueries to achieve more functionality.
The following example shows that there are two tables, tbl_a,tbl_b, and two tables with KeyCol columns. Now we compare two tables, the data in the KeyCol column in the tbl_a can be found in the data of the KeyCol column in Tbl_b, return the result ' matched ', and if not found, return the result ' unmatched '.
To implement this function, you can use the following two statements

--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )
THEN ‘Matched‘
ELSE ‘Unmatched‘ END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B
WHERE tbl_A.keyCol = tbl_B.keyCol )
THEN ‘Matched‘
ELSE ‘Unmatched‘ END Label
FROM tbl_A;

Six, use the aggregate function in the case function

Suppose you have one of the following tables

School Number (STD_ID)	Course ID (class_id)	Course Name (class_name)	Major in Flag (MAIN_CLASS_FLG)
100	1	Economics	Y
100	2	Historical studies	N
200	2	Historical studies	N
200	3	Archaeology	Y
200	4	Computer	N
300	4	Computer	N
400	5	Chemical	N
500	6	Mathematical	N

Some students choose to take several courses at the same time (100,200) Some students choose only one course (300,400,500). Students taking multiple courses are asked to choose a course as their major, with the major flag written in Y. Students who choose only one course, majoring in flag n (in fact, if written in Y, will not have the following trouble, in order to give examples, please include more).
Now we're going to query this table according to the following two conditions

1, the person who only took one course, returned the ID of that course,

2, the person who takes multiple courses, returns the selected master course ID

The simple idea is to execute two different SQL statements to query.

Condition 1: Students with only one course selected

SELECT std_id, MAX(class_id) AS main_class
FROM Studentclass
GROUP BY std_id
HAVING COUNT(*) = 1;

Execution Results 1

STD_ID   MAIN_class------   ----------
300      4
400      5
500      6

Condition 2: Students who choose multiple courses

SELECT std_id, class_id AS main_class
FROM Studentclass
WHERE main_class_flg = ‘Y‘ ;

If you use the case function, we can solve the problem with just one SQL statement, as shown below

SELECT  std_id,
CASE WHEN COUNT(*) = 1  --只选择一门课程的学生的情况
THEN MAX(class_id)
ELSE MAX(CASE WHEN main_class_flg = ‘Y‘
THEN class_id
ELSE NULL END
)
END AS main_class
FROM Studentclass
GROUP BY std_id;

Run results

STD_ID   MAIN_class
------   ----------
100      1
200      3
300      4
400      5
500      6

Identify the QR code in the image and collect the full Python video

In MySQL, case, then else end usage