How to use the CASE function in MySQL to perform multi-condition judgment

How to use the CASE function in MySQL to perform multi-condition judgment

In the MySQL database, the CASE function is a very powerful tool that can perform multi-condition judgment in the query statement. It allows us to return different results based on different conditions to meet complex business needs. This article will introduce how to use the CASE function in MySQL to perform multi-condition judgment and provide some code examples.

The syntax structure of the CASE function is as follows:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   WHEN condition3 THEN result3
   ...
   ELSE result
END

In this syntax structure, we can return different results (result) according to different conditions. When all conditions are not met, you can use the ELSE keyword to specify a default result.

Now, suppose we have a table named students that stores the students' names, ages, and grades. We need to classify students according to their grades and determine whether they are teenagers based on their age. The following is a sample code that uses the CASE function for multi-condition judgment:

SELECT 
   name,
   age,
   grade,
   CASE
      WHEN grade >= 90 THEN '优秀'
      WHEN grade >= 80 THEN '良好'
      WHEN grade >= 70 THEN '中等'
      ELSE '不及格'
   END AS level,
   CASE
      WHEN age >= 12 AND age <= 18 THEN '青少年'
      ELSE '成年人'
   END AS category
FROM students;

In the above code, we use two CASE functions to judge the student's grades and age, and return different results respectively. The grades are divided according to the scores. If the score is greater than or equal to 90 points, it is excellent; if the score is greater than or equal to 80 points, it is good; if the score is greater than or equal to 70 points, it is average; otherwise, it is failed. Classification is based on age. If the age is between 12 and 18 years old, it is a teenager; otherwise, it is an adult.

In addition to basic conditional judgment, the CASE function can also use logical operators to combine multiple conditions. Suppose we need to judge students' grades and attendance respectively, and give different rewards based on the judgment results. The code example is as follows:

SELECT 
   name,
   attendance,
   grade,
   CASE
      WHEN grade >= 90 AND attendance = '100%' THEN '获奖'
      WHEN grade >= 80 OR attendance = '100%' THEN '鼓励'
      ELSE '加油'
   END AS reward
FROM students;

In the above code, we use two conditional judgments, one based on the grades and the other. Different rewards are given for attendance. If the score is greater than or equal to 90 points and the attendance is 100%, you will be rewarded; if the score is greater than or equal to 80 points or the attendance is 100%, you will be encouraged; in other cases, cheers.

Through the above code examples, we have learned how to use the CASE function in MySQL to perform multi-condition judgment. The CASE function not only simplifies complex conditional judgment logic, but also provides a flexible syntax structure, allowing us to return different results based on different conditions. In actual development, we can flexibly use the CASE function according to specific business needs to make database operations more efficient and convenient.

The above is the detailed content of How to use the CASE function in MySQL to perform multi-condition judgment. For more information, please follow other related articles on the PHP Chinese website!