sql case when usage

sql case when usage is to allow different operations to be performed based on different conditions. Whether it's a simple classification or complex nested conditions, the CASE WHEN statement provides a flexible and readable way to handle different situations. By mastering and using the CASE WHEN statement proficiently, we can better process and analyze data.

The CASE WHEN statement in SQL is a conditional statement that allows us to perform different operations based on different conditions. This is very useful in querying and data processing because it provides a flexible way to handle different situations. In this article, we will introduce the usage of CASE WHEN statement in SQL in detail.

In SQL, the general form of the CASE WHEN statement is as follows:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END

The CASE keyword is used to start the CASE WHEN statement, followed by a series of WHEN clauses, each WHEN clause Contains a condition and an associated result. If the condition is true, the corresponding result is executed. If all conditions are false, the default result in the ELSE clause is executed. Finally, the END keyword is used to end the CASE WHEN statement.

The following is a simple example showing the basic usage of the CASE WHEN statement:

SELECT
customer_id,
order_date,
CASE
WHEN total_amount > 1000 THEN 'High'
WHEN total_amount > 500 THEN 'Medium'
ELSE 'Low'
END AS order_category
FROM
orders

In this example, we divide the orders into three categories based on the total amount of the order: high, medium and low. If the total amount of the order exceeds 1000, it is classified as "High"; if the total amount exceeds 500, it is classified as "Medium", otherwise it is classified as "Low". This example shows how to use the CASE WHEN statement to add a calculated column to a query.

CASE WHEN statements can also be nested, so that we can handle more complex conditions. For example, we can classify orders in more detail based on the total amount of the order and the status of the order. Here is an example:

SELECT
customer_id,
order_date,
CASE
WHEN total_amount > 1000 THEN
CASE
WHEN order_status = 'Completed' THEN 'High and Completed'
ELSE 'High and Incomplete'
END
WHEN total_amount > 500 THEN
CASE
WHEN order_status = 'Completed' THEN 'Medium and Completed'
ELSE 'Medium and Incomplete'
END
ELSE
CASE
WHEN order_status = 'Completed' THEN 'Low and Completed'
ELSE 'Low and Incomplete'
END
END AS order_category
FROM
orders

In this example, we first classify the orders based on their total amount and then further segment them based on the status of the order. If the total amount of the order is greater than 1,000, and the order status is "Completed", it is classified as "High and Completed"; if the total amount of the order is greater than 1,000, but the order status is not "Completed", it is classified as "High" and Incomplete", and so on.

Summary

The CASE WHEN statement is very useful in SQL queries and can perform different operations based on different conditions. Whether it's a simple classification or complex nested conditions, the CASE WHEN statement provides a flexible and readable way to handle different situations. By mastering and using the CASE WHEN statement proficiently, we can better process and analyze data.

The above is the detailed content of sql case when usage. For more information, please follow other related articles on the PHP Chinese website!