How to use Case when statement in SQL

This article mainly introduces how to use Case when statement in SQL, the article is very detailed, has certain reference value, interested friends must read it!

Case comes in two formats. Simple Case functions and Case search functions.

--Simple Case Function CASE sexWHEN '1' THEN 'MALE'WHEN '2' THEN 'FEMALE'ELSE 'OTHER' END--Case Search Function CASE WHEN sex = '1' THEN 'MALE'WHEN sex = '2' THEN 'FEMALE'ELSE 'OTHER' END

Both of these methods can achieve the same function. Simple Case functions are relatively simple to write, but compared to Case search functions, there are some limitations in terms of functionality, such as writing judgments.

Another caveat is that the Case function returns only the first value that matches the condition, and the rest of the Case is automatically ignored.

--For example, in the following SQL, you can never get the result "second class" CASE WHEN col_1 IN ('a ', ' b') THEN 'first class'WHEN col_1 IN ('a ') THEN 'SECOND CLASS'ELSE'OTHER' END

Let's see what can be done with the Case function.

First, known data is grouped and analyzed in another way.

The following data is available:(For clarity, I did not use the country code, but used the country name directly as the Primary Key)

Country Population China 600 USA 100 Canada 100 UK 200 France 300 Japan 250 Germany 200 Mexico 50 India 250

According to the population data of this country, count the population of Asia and North America. You should get the following result.

Continent Population Asia 1100 North America 250 Others 700

To solve this problem, what would you do? Generating a View with Continent Code is one solution, but it is difficult to dynamically change the statistics.

If you use the Case function, the SQL code is as follows:

SELECT SUM(population),CASE countryWHEN 'China' THEN 'ASIA'WHEN 'INDIA' THEN 'ASIA'WHEN 'JAPAN' THEN 'ASIA'WHEN 'AMERICA' THEN 'NORTH AMERICA'WHEN 'CANADA' THEN 'NORTH AMERICA'WHEN 'MEXICO' THEN 'NORTH AMERICA'ELSE 'OTHER' ENDFROM Table_AGROUP BY CASE countryWHEN '中国' THEN 'ASIA'WHEN 'INDIA' THEN 'ASIA'WHEN 'JAPAN' THEN 'ASIA'WHEN 'AMERICA' THEN 'NORTH AMERICA'WHEN 'CANADA' THEN 'NORTH AMERICA'WHEN 'MEXICO' THEN 'NORTH AMERICA'ELSE 'OTHER' END;

Similarly, we can use this method to judge the wage level and count the number of people in each level. SQL code is as follows:

SELECTCASE WHEN salary 500 AND salary 600 AND salary 800 AND salary 1000 ) The above is "How to use Case when statement in SQL" All the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!