In Oracle, when the then else end is applied

For the use of case, the simple case function is 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

Searches the case expression, using conditions 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, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function.

There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored.

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

The code is as follows

Case is col_1 in (' A ', ' B ') then ' first Class '

When Col_1 in (' a ') then ' second class '

Else ' other ' END

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

One, the known data in a different way to group, analysis.

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

Country (country) population (population)

USA 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. The following result should be obtained.

Continent population

Asia 1100

North America 250

Other 700

What would you do to solve this problem? Creating a view with a continent code is a workaround, 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 ' Mexico ' 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 ' Mexico ' then ' North America '

Else ' other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows

SELECT

Case when salary <= ' 1 '

When salary > Salary <= 2 '

When salary > Salary <= 3 '

When salary > Salary <= "4"

ELSE NULL END Salary_class,

COUNT (*)

From Table_a

GROUP by

Case when salary <= ' 1 '

When salary > Salary <= 2 '

When salary > Salary <= 3 '

When salary > Salary <= "4"

ELSE NULL END;

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

Have the following data

Country (country) gender (sex) population (population)

China 1 340

China 2 260

United States 1 45

United States 2 55

Canada 1 51

Canada 2 49

United Kingdom 1 40

United Kingdom 2 60

Grouped according to country and gender, the results are as follows

Country men and women

China 340 260

United States 45 55

Canada 51 49

United Kingdom 40 60

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

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

The code is as follows SELECT country,

SUM (case if sex = ' 1 ' Then

Population ELSE 0 END),--Male population

SUM (case if 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 the two-dimensional table, which fully shows the strong case function.

Third, use the case function in check.

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

Let's give an example.

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

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 clerk was met, and the male clerk 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, case if Val like ' a% ' then ' 1 ' when Val like ' abcd% ' then ' 2 ' else ' 999 ' End Case from Feng_test;

ID Case---------------------1 1 2 1

According to my own experience, I think that when using case, this is much like ASP case when you use the PHP swicth case to develop the usage of the statement, as long as a little basic know I think the case in SQL is actually very good understanding

In Oracle, when the then else end is applied