SQL case then else usage

CASE has two formats:

The simple CASE function compares an expression with a group of simple expressions to determine the result.

The CASE search function calculates a group of Boolean expressions to determine the result.

Both formats support the optional ELSE parameter.

Syntax
Simple CASE functions:

CASE input_expression
WHEN when_expression THEN result_expression
[... N]
    [
ELSE else_result_expression
END

CASE Search function:

CASE
WHEN Boolean_expression THEN result_expression
[... N]
    [
ELSE else_result_expression
END

Parameters
Input_expression

Is the expression calculated when the simple CASE format is used. Input_expression is any valid Microsoft® SQL Server™ expression.

WHEN when_expression

Simple expressions compared with input_expression in simple CASE format. When_expression is any valid SQL Server expression. The data type of Input_expression and when_expression must be the same or implicit conversion.

N

Placeholder, indicating that multiple WHEN when_expression THEN result_expression clauses or WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

The expression returned when input_expression = when_expression is set to TRUE or Boolean_expression is set to TRUE. Result expression is any valid SQL Server expression.

ELSE else_result_expression

The expression returned when the value of the comparison operation is not TRUE. If this parameter is omitted and the value of the comparison operation is not TRUE, CASE returns NULL. Else_result_expression is any valid SQL Server expression. Else_result_expression and all result_expressions must have the same data type or be implicitly converted.

WHEN Boolean_expression

The Boolean expression calculated when the CASE search format is used. Boolean_expression is any valid Boolean expression.

Result type
Returns the highest priority rule type from the type set of result_expressions and the optional else_result_expression. For more information, see data type precedence.

Result value
Simple CASE functions:
Calculate input_expression, and then calculate the input_expression = when_expression of each WHEN clause in the specified order.

Returns the result_expression of the first (input_expression = when_expression) value that is TRUE.

If no input_expression = when_expression is set to TRUE, SQL Server returns else_result_expression when the ELSE clause is specified. If no ELSE clause is specified, NULL is returned.

CASE Search function:
Evaluate the Boolean_expression of each WHEN clause in the specified order.

Returns the result_expression of the first Boolean_expression with the value TRUE.

If no Boolean_expression is set to TRUE, SQL Server returns else_result_expression when the ELSE clause is specified. If no ELSE clause is specified, NULL is returned.

-- Simple Case function
CASE sex
WHEN '1' THEN 'male'
WHEN '2' THEN 'female'
ELSE 'others' END
-- Case search function
Case when sex = '1' THEN 'male'
WHEN sex = '2' THEN 'female'
ELSE 'others' END

These two methods can achieve the same function. Simple Case functions are relatively simple in writing, but compared with Case search functions, there are some functional limitations, such as writing Case functions.
Note that the Case function returns only the first value that meets the condition, and the rest of the Case will be automatically ignored.

Example:
There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when Mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table

CASE may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can use CASE in the WHERE clause.

First, let's take a look at the CASE syntax. In a general SELECT statement, the syntax is as follows:

SELECT <myColumnSpec> =
CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END

In the above code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:

USE pubs
GO
SELECT
Title,
'Price Range' =
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END
FROM titles
Order by price
GO

This is a typical example of CASE, but you can do more with CASE. For example, the CASE in the group by clause below:

SELECT 'number of tidles ', Count (*)
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END
GO

You can even combine these options to add an order by clause, as shown below:

USE pubs
GO
SELECT
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
End as Range,
Title
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END,
Title
ORDER
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END,
Title
GO