How to use case in mysql

The CASE statement is used to return different values ​​based on conditions. Its syntax is: CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ELSE default_result END. It can be used to: define conditions, specify results, and provide default results. Other uses include value conversion, checking for NULL values, and selecting the largest or smallest value.

Usage of CASE in MySQL

The CASE statement is used in MySQL to return different values ​​based on specified conditions. . Its syntax is as follows:

<code class="sql">CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ELSE default_result
END</code>

How to use the CASE statement?

1. Define conditions: Use the WHEN clause to specify the conditions to be evaluated.
2. Specify the result: If the condition is true, return the corresponding result.
3. Provide default results: Use the ELSE clause to specify a default value when all conditions are not true.

Example:

Suppose there is a table named customers with the following columns:

• customer_id: Customer ID
• customer_type: Customer type (such as Standard, Premium)
• discount: According to the customer Discounts provided by type

To calculate discounts based on customer types, you can use the CASE statement:

<code class="sql">SELECT customer_id,
       CASE
         WHEN customer_type = 'Standard' THEN 0.1
         WHEN customer_type = 'Premium' THEN 0.2
         ELSE 0
       END AS discount
FROM customers;</code>

Other usage:

The CASE statement also Can be used for:

• Convert values ​​to other types (using the CAST() function)
• Check for NULL values ​​(use IS NULL and IS NOT NULL)
• Select the maximum or minimum value from multiple conditions (using the GREATEST() and LEAST() functions)

Note:

• CASE statement The conditions in must all be of the same type.
• The ELSE clause is optional, but if you do not specify a default result, a NULL value may be returned.
• CASE statements can be nested to create more complex result sets.

The above is the detailed content of How to use case in mysql. For more information, please follow other related articles on the PHP Chinese website!