How to use the CASE function to implement multiple conditional judgments in MySQL

How to use the CASE function in MySQL to implement multiple conditional judgments

In MySQL, we often need to conditionally judge data and perform different operations based on different conditions. The CASE function is a very useful tool that can implement multiple conditional judgments and perform different operations based on different conditions.

The syntax of the CASE function is as follows:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END

Among them, condition is a conditional expression, which can be any expression that can return a Boolean value; result is the operation that needs to be performed when the corresponding condition is met.

The following are some examples showing how to use the CASE function to implement multiple conditional judgments:

1. Single conditional judgment:

Suppose we have a students table , containing student information and grades. Now we want to judge the student's grade based on their scores. When the score is greater than or equal to 80 points, it is A, when the grade is greater than or equal to 60 points and less than 80 points, it is B, and when the grade is less than 60 points, it is C. This can be achieved using the following code:

SELECT
name,
score,
CASE

  WHEN score >= 80 THEN 'A'
  WHEN score >= 60 THEN 'B'
  ELSE 'C'

END as grade
FROM
students;

2. Multi-condition judgment:

In the above example, we only performed a single-condition judgment. If we need to judge based on multiple conditions, we can use the nested CASE function.

Suppose we have an orders table containing order information and total amount. Now we want to judge the level of the order based on the total amount. When the total amount is greater than or equal to 1,000, it is a high-level order. When the total amount is greater than or equal to 500 and less than 1,000, it is a mid-level order. When the total amount is less than 500, it is a low-level order. This can be achieved using the following code:

SELECT
order_id,
total_amount,
CASE

  WHEN total_amount >=1000 THEN '高级订单'
  WHEN total_amount >=500 THEN '中级订单'
  ELSE '低级订单'

END as level
FROM
orders;

3. Application of CASE function in update operations:

In addition to using the CASE function in query statements, we can also use the CASE function in update operations. For example, if we want to update students' grades based on their grades, we can use the following code to achieve this:

UPDATE students
SET grade =
CASE

  WHEN score >= 80 THEN 'A'
  WHEN score >= 60 THEN 'B'
  ELSE 'C'

END;

Summary:

In MySQL, the CASE function is a very useful tool that can realize multiple conditional judgments and perform different operations based on different conditions. Whether in query statements or update operations, the CASE function can help us make conditional judgments concisely and flexibly. I hope the sample code in this article can help readers better understand and apply the CASE function.

The above is the detailed content of How to use the CASE function to implement multiple conditional judgments in MySQL. For more information, please follow other related articles on the PHP Chinese website!