SQL Server CASE 表达式

In SQL Server, the CASE expression can perform branching based on certain conditions and return the corresponding results. It is similar to the if-else statement in programming languages and can be used in operations such as querying and updating.

Syntax

The basic syntax of the CASE expression is as follows:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE else_result
END

Here, expression is the expression to be evaluated, which can be a column name, variable, or constant; value1, value2, ... are the values to be matched; result1, result2, ... are the results corresponding to the matched values; and else_result is the default result when none of the matched values are satisfied. Each WHEN clause must be followed by a corresponding THEN clause, and there can be multiple clauses, but only the result of the first matching clause will be returned.

In addition to the basic syntax, the CASE expression can also use search syntax. The syntax for the search syntax is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

Here, condition1, condition2, ... are the conditional expressions to be evaluated, which can be any logical expression, including operators such as greater than, less than, and equal to.

Usage

The CASE expression is widely used in SQL queries and can be used in the following scenarios:

• Return different results based on different conditions
• Classify and summarize query results
• Transform and process query results

Examples

Here are two examples of using the CASE expression.

Example 1: Return different results based on different conditions

Suppose we have a table containing student grades, including the student’s name, subject, and score. We want to query the total score of each student and return “Pass” when the total score is greater than or equal to 200, otherwise return “Fail”. We can use the following SQL statement:

SELECT name,
       SUM(CASE WHEN subject = 'math' THEN score ELSE 0 END +
           CASE WHEN subject = 'english' THEN score ELSE 0 END) AS total_score,
       CASE WHEN SUM(CASE WHEN subject = 'math' THEN score ELSE 0 END +
                 CASE WHEN subject = 'english' THEN score ELSE 0 END) >= 200
            THEN 'Yes' ELSE 'No' END AS result
FROM grades
GROUP BY name;

This query will return the total score and result of each student, for example:

Example 2: Classify and summarize query results

Suppose we have a table containing product sales records, including the product name, sales quantity, and sales date. We want to summarize the sales of each product in different months and classify them by product name and month.

We can use the CASE expression to achieve this requirement. Here is the sample code:

SELECT ProductName,
       CASE
         WHEN MONTH(SalesDate) = 1 THEN 'January'
         WHEN MONTH(SalesDate) = 2 THEN 'February'
         WHEN MONTH(SalesDate) = 3 THEN 'March'
         WHEN MONTH(SalesDate) = 4 THEN 'April'
         WHEN MONTH(SalesDate) = 5 THEN 'May'
         WHEN MONTH(SalesDate) = 6 THEN 'June'
         WHEN MONTH(SalesDate) = 7 THEN 'July'
         WHEN MONTH(SalesDate) = 8 THEN 'August'
         WHEN MONTH(SalesDate) = 9 THEN 'September'
         WHEN MONTH(SalesDate) = 10 THEN 'October'
         WHEN MONTH(SalesDate) = 11 THEN 'November'
         WHEN MONTH(SalesDate) = 12 THEN 'December'
       END AS MonthName,
       SUM(SalesQuantity) AS TotalSales
FROM Sales
GROUP BY ProductName,
         CASE
           WHEN MONTH(SalesDate) = 1 THEN 'January'
           WHEN MONTH(SalesDate) = 2 THEN 'February'
           WHEN MONTH(SalesDate) = 3 THEN 'March'
           WHEN MONTH(SalesDate) = 4 THEN 'April'
           WHEN MONTH(SalesDate) = 5 THEN 'May'
           WHEN MONTH(SalesDate) = 6 THEN 'June'
           WHEN MONTH(SalesDate) = 7 THEN 'July'
           WHEN MONTH(SalesDate) = 8 THEN 'August'
           WHEN MONTH(SalesDate) = 9 THEN 'September'
           WHEN MONTH(SalesDate) = 10 THEN 'October'
           WHEN MONTH(SalesDate) = 11 THEN 'November'
           WHEN MONTH(SalesDate) = 12 THEN 'December'
         END
ORDER BY ProductName,
         MONTH(SalesDate)

In the above code, we used the CASE expression to convert each sales date’s corresponding month to the month’s name, and then grouped and summed the results, and finally sorted them by product name and month.

Here are the sample results:

From the above results, we can see that we have successfully classified and summarized the sales volume of each product in different months and sorted it by product name and month.

Conclusion

The CASE expression is a very powerful expression that can be used to logically judge different conditions in a query and return different results. Through the CASE expression, we can easily achieve complex logical operations and data conversion. Additionally, when used in combination with other expressions, the CASE expression can also play a greater role. However, it is important to pay attention to the correctness of the syntax and the reasonableness of the logic when using the CASE expression to avoid incorrect results.