Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use case when in SQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The usage of case when in SQL

Case has two formats: the simple Case function and the Case search function.

Simple Case function:

CASE sexWHEN'1' THEN 'male' WHEN'0' THEN 'female' ELSE 'other' END

Case search function:

CASE WHEN sex ='1' THEN 'male' WHEN sex ='0' THEN 'female' ELSE 'other' END

Obviously, the simple Case function is better than simplicity, but it only applies to this single-valued comparison of a single field, while the advantage of the Case search function is that it applies to all comparison situations.

One more thing to note is that after the Case function satisfies a certain condition, the remaining conditions will be automatically ignored, so even if multiple conditions are met, only the first condition will be recognized in the execution process.

When using CASE WHEN, you can think of it as a logically anonymous field, the field value is confirmed by conditions, and you can use as to define aliases when you need to use the field name. This is still abstract, just take a look at the CASE WHEN use case below.

Working with scen

1. The known data can be grouped and analyzed in a certain way.

According to the population data of this country, count the population of Asia and North America. Use the following SQL:

SELECT CASE countryWHEN 'China,' THEN, Asia, WHEN, India, THEN, Asia, WHEN, Japan, THEN, Asia, WHEN, United States, THEN, North America, WHEN, Canada, THEN, North America, WHEN, Mexico, THEN, North America, ELSE, other END as continents. SUM (population) as' population 'FROM testGROUP BY CASE countryWHEN' China 'THEN' Asia 'WHEN' India 'THEN' Asia 'WHEN' Japan 'THEN' Asia 'WHEN' United States' THEN 'North America' WHEN 'Canada' THEN 'North America' WHEN 'Mexico' THEN 'North America' ELSE 'other' END

Both CASE WHEN here are equivalent to one field, but it is worth mentioning that the then value of the second CASE WHEN does not need to specify what continent it is, it is just used to group records, so the value after THEN can only distinguish between the three records, and GROUP BY can also be written as:

GROUP BY CASE countryWHEN 'China' THEN 0WHEN 'India' THEN 0WHEN 'Japan' THEN 0WHEN 'United States' THEN 1WHEN 'Canada' THEN 1WHEN 'Mexico' THEN 1ELSE 2 END

2. Use a SQL statement to complete the grouping of different conditions.

The data are as follows:

Use the Case function to complete grouping by country and gender. Use the following SQL:

SELECT country,SUM (CASE WHEN sex ='1' THEN population ELSE 0 END)-- male population SUM (CASE WHEN sex ='2' THEN population ELSE 0 END)-- female population FROM Table_AGROUP BY country

The results are as follows:

With regard to the first CASE WHEN explanation:

CASE WHEN sex ='1' THENpopulation ELSE 0 END

When the sex of the record is 1, the value of this field is the population value of the record, otherwise it is 0, so the male population of a country can be calculated.

This is the end of the article on "how to use case when in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.