How Oracle case is used

Use of case, simple case function
A simple case expression that uses an expression to determine the return value.

Grammar:

Case Search_expression

When Expression1 THEN RESULT1

When Expression2 THEN result2

...

When Expressionn THEN Resultn

ELSE Default_result

Search the case expression, using criteria to determine the return value.

Grammar:

Case

When Condition1 THEN RESULT1

When Condistion2 THEN result2

...

When Condistionn THEN Resultn

ELSE Default_result

End

Cases:

Select product_id,product_type_id,

Case

When Product_type_id=1 then ' book '

When product_type_id=2 then ' video '

When product_type_id=3 then ' DVD '

When product_type_id=4 then ' CD '

Else ' Magazine '

End

From Products

In both of these ways, you can achieve the same functionality. The simple case function is relatively concise, but there are some limitations to the function, such as writing a judgment, compared with the search function.

There is also a problem to note that the case function returns only the first qualifying value, and the remainder of the case is automatically ignored.

For example, the following SQL, you can never get the "second class" results

The code is as follows

Case when col_1 in (' A ', ' B ') THEN ' first class '

When Col_1 in (' a ') THEN ' Class II '

Else ' other ' end

Let's take a look at what we can do with the case function.

First, the known data is grouped and analyzed in a different way.

Have the following data: (in order to see more clearly, I did not use the country code, but directly using the country name as primary Key)

Country (country) population (population)

China 600

United States 100

Canada 100

United Kingdom 200

France 300

Japan 250

Germany 200

Mexico 50

India 250

According to the population data of this country, the population of Asia and North America is counted. Should get the following result.

Continent population

Asia 1100

North America 250

Other 700

What would you do to solve the problem? Creating a view with state code is a solution, but it is difficult to dynamically change the way statistics are used.

If you use the case function, the SQL code is as follows

SELECT SUM (population),

Case Country

When ' China ' THEN ' Asia '

When ' India ' THEN ' Asia '

When ' Japan ' THEN ' Asia '

When ' America ' THEN ' North America '

When ' Canada ' THEN ' North America '

When ' Mexican ' THEN ' North America '

Else ' other ' end

From Table_a

GROUP by Case Country

When ' China ' THEN ' Asia '

When ' India ' THEN ' Asia '

When ' Japan ' THEN ' Asia '

When ' America ' THEN ' North America '

When ' Canada ' THEN ' North America '

When ' Mexican ' THEN ' North America '

Else ' other ' end;

Similarly, we can use this method to determine the level of wages, and statistics of the number of each level. The SQL code is as follows

SELECT

Case when salary <= THEN ' 1 '

When salary > Salary <= THEN ' 2 '

When salary > Salary <= THEN ' 3 '

When salary > Salary <= 1000 THEN ' 4 '

ELSE NULL End Salary_class,

COUNT (*)

From Table_a

GROUP by

Case when salary <= THEN ' 1 '

When salary > Salary <= THEN ' 2 '

When salary > Salary <= THEN ' 3 '

When salary > Salary <= 1000 THEN ' 4 '

ELSE NULL end;

Second, use a SQL statement to complete the grouping of different conditions.

Have the following data

National (country) gender (sex) population (population)

China 1 340

China 2 260

The United States 1 45

The United States 2 55

Canada 1 51

Canada 2 49

United Kingdom 1 40

United Kingdom 2 60

Grouped by country and gender, the results are as follows

National men and women

China 340 260

The United States 45 55

Canada 51 49

United Kingdom 40 60

In general, the Union can also be implemented with a statement to query. But that increases consumption (two select parts), and the SQL statement is longer.

Here is an example of using the case function to complete this function

The code is as follows
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_a

GROUP by country;

In this way, we use Select to complete the output form of two-dimensional table, fully show the powerful case function.

Third, use the case function in check.

Using the case function in check is a very good solution in many cases. There may be a lot of people who don't check at all, so I suggest you try using check in SQL after looking at the example below.

Now let's take an example

Company A, the company has a rule, the female staff must pay more than 1000 yuan. If you use check and case to behave, as shown below

The code is as follows
CONSTRAINT check_salary Check

(Case when sex = ' 2 ')

THEN Case when salary > 1000

THEN 1 ELSE 0 End

ELSE 0 End)

If you simply use check, as shown below

The code is as follows

CONSTRAINT check_salary Check

(Sex = ' 2 ' and salary > 1000)

The condition of the female staff was met, and the male staff could not enter it.

Instance

The code is as follows

CREATE TABLE Feng_test (ID number, Val varchar2 (20);

Insert into Feng_test (Id,val) VALUES (1, ' ABCDE ');
Insert into Feng_test (Id,val) VALUES (2, ' abc ');
Commit

Sql>select * from Feng_test;

ID val
-------------------
1 ABCDE
2 ABC

Sql>select ID
, when Val like ' a% ' then ' 1 '
When Val like ' abcd% ' then ' 2 '
Else ' 999 '
End case
From Feng_test;

ID case
---------------------
1 1
2 1

Based on my own experience, I would have thought that when using the case, this is much like an ASP when the use of the word developed in PHP swicth, as long as there is a basic knowledge that I think the case in SQL is actually very good understanding.