This article will show you how to use SQL CASE WHEN in detail

Simple CASE WHEN function:

CASE SCORE WHEN 'A' THEN 'Excellent' ELSE 'Failed' END
CASE SCORE WHEN 'B' THEN 'Good' ELSE 'Failed' END
CASE SCORE WHEN 'C' THEN 'Medium' ELSE 'Failed' END

This is equivalent to using the CASE WHEN conditional expression function:

CASE WHEN SCORE = 'A' THEN 'Excellent'
     WHEN SCORE = 'B' THEN 'Good'
     WHEN SCORE = 'C' THEN 'Medium' ELSE 'Failed' END

The value after THEN should be of the same type as the value after ELSE, otherwise an error will be reported. as follows:

CASE SCORE WHEN 'A' THEN 'Excellent' ELSE 0 END

If the data types of 'Excellent' and 0 are inconsistent, an error will be reported:

[Err] ORA-00932: inconsistent data types: expected CHAR, got NUMBER

The simple CASE WHEN function can only handle some simple business scenarios, while the writing rules of the CASE WHEN conditional expression are more flexible.

CASE WHEN conditional expression function: similar to the IF ELSE statement in JAVA.

Format:

CASE WHEN condition THEN result

[WHEN...THEN...]

ELSE result

END

condition is an expression that returns a Boolean type. If the expression returns true, the entire function returns the value of the corresponding result. If all expressions are false, the value of the result after ElSE is returned. If the ELSE clause is omitted, NULL is returned.

The following are some common scenarios.

Scenario 1: There is a score, score < 60 returns a failed score, score >= 60 returns a passed score, and score >= 80 returns an excellent score

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN 'failed'
        WHEN score >= 60 AND score < 80 THEN 'pass'
        WHEN score >= 80 THEN 'Excellent'
        ELSE 'Exception' END) AS REMARK
FROM
    TABLE

Note : If you want to check whether the score is null, WHEN score = null THEN 'absent from the exam', this is an incorrect way of writing. The correct way to write it is:

CASE WHEN score IS NULL THEN 'Absent from the exam' ELSE 'Normal' END

Scenario 2: The teacher needs to count how many boys and girls there are in the class, and how many boys and girls have passed the exam. The teacher needs to use a SQL statement to output the results.

The table structure is as follows: In the STU_SEX field, 0 represents a boy and 1 represents a girl.

SELECT 
	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
	THTF_STUDENTS

The output is as follows:

Scenario 3: Classic row-to-column conversion and statistical analysis with aggregate functions

Now it is required to count the total water consumption, electricity consumption, and heat consumption of each city, and use a SQL statement to output the results

The energy consumption table is as follows: E_TYPE represents the energy consumption type, 0 represents water consumption, 1 represents electricity consumption, and 2 represents heat consumption

SELECT 
	E_CODE,
	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--Water consumption SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--Electricity consumption SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--Heat consumption FROM 
	THTF_ENERGY_TEST
GROUP BY
	E_CODE

The output is as follows :

Scenario 4: Using subqueries in CASE WHEN

Calculate the electricity cost based on the city’s electricity consumption. Assume that the unit price of electricity consumption is divided into three levels, and use the corresponding prices to calculate the cost according to different energy consumption values.

The price list is as follows:

When the energy consumption value is less than 10, the P_PRICE value when P_LEVEL=0 is used. When the energy consumption value is greater than 10 and less than 30, the P_PRICE value when P_LEVEL=1 is used.

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

Scenario 5: Combined with the max aggregation function

The CASE WHEN function is simple to use and easy to understand. This article only gives a brief introduction to its use. It is necessary to use it flexibly according to different business scenarios in actual work.

Summarize

This article ends here. I hope it can be helpful to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM!