On the usage of case statements in SQL _mssql

How to use case in SQL

Case has two formats. Simple case function and case search function.

Copy Code code as follows:

--Simple Case function

Case Sex

When ' 1 ' THEN ' male '

When ' 2 ' THEN ' woman '

Else ' other ' end

--case search function

case when sex = ' 1 ' THEN ' man '

When sex = ' 2 ' THEN ' female '

Else ' other ' end

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" result

Copy Code code 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:

Copy Code code 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;

Copy Code code 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

Copy Code code 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, this company has a rule, the female staff's salary must be higher than the block. If you use check and case to behave, as shown below

Copy Code code as follows:

CONSTRAINT check_salary Check

(Case when sex = ' 2 ')

THEN Case when salary > 1000

THEN 1 ELSE 0 End

ELSE 1 end = 1)

If you simply use check, as shown below

Copy Code code 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.

Four, according to the conditional have selected update.

example, there are the following update conditions

Staff with a salary reduction

Wages in between to the staff, the increase in wages%

It is easy to consider the option of executing the two UPDATE statement as follows

Copy Code code as follows:

--condition

UPDATE Personnel

SET salary = salary * 0.9

WHERE Salary >= 5000;

--condition

UPDATE Personnel

SET Salary = salary * 1.15

WHERE Salary >= and salary < 4600;

But it's not as simple as it might seem, assuming there's a personal wage block. First of all, according to the conditions, wages reduced to become wages. Then run the second SQL, because this person's salary is within the range, need to increase%, the last person's salary is not reduced, but increased. If it were to be done in reverse, then the person who paid would turn into a reduction in wages. Regardless of how absurd this regulation is, if you want an SQL statement to implement this function, we need to use the case function. The code is as follows:

Copy Code code as follows:

UPDATE Personnel

SET salary = case when salary >= 5000

THEN Salary * 0.9

When salary >= and salary < 4600

THEN Salary * 1.15

ELSE salary end;

It is important to note here that the last line of else salary is required, and if this is not the case, the wages of those who do not meet these two conditions will be written null, and that would be a bad thing. The default value for the else part in the case function is null, and this is where you need to be aware.

This method can also be used in many places, such as changing the primary key such dirty.

In general, to two data primary Key,a and B exchange, the need for temporary storage, copy, read back the data of the three processes, if the use of case functions, everything becomes much simpler.

P_key Col_1 col_2

A 1 sheets of three

B 2 Dick

C 3 Harry

Suppose you have data, you need to exchange primary keys A and B. With the case function, the code is as follows

Copy Code code as follows:

UPDATE sometable

SET P_key = case when P_key = ' a '

THEN ' B '

When p_key = ' B '

THEN ' a '

ELSE P_key End

WHERE P_key in (' A ', ' B ');

The same can also be exchanged for two unique key. It should be noted that if there is a need to exchange the primary key, most of the original design of the table is not in place, it is recommended to check the design of the table is appropriate.

Five, two table data is consistent check.

The case function is different from the Decode function. In the case function, you can use Between,like,is null,in,exists and so on. For example, the use of in,exists, can be subqueries, so as to achieve more functionality.

Here is an example to illustrate that there are two tables, tbl_a,tbl_b, and KeyCol columns in all two tables. Now we are comparing two tables, the KeyCol column in Tbl_a can be found in the Tbl_b keycol column data, return the result ' matched ', if not found, return the result ' unmatched '.

To implement this feature, you can use the following two statements

Copy Code code as follows:

--When using in

SELECT KeyCol,

Case when KeyCol in (SELECT keycol from Tbl_b)

THEN ' matched '

ELSE ' unmatched ' end Label

From Tbl_a;

--When using the Exists

SELECT KeyCol,

Case when EXISTS (SELECT * from Tbl_b

WHERE Tbl_a.keycol = tbl_b.keycol)

THEN ' matched '

ELSE ' unmatched ' end Label

From Tbl_a;

The results of using in and exists are the same. You can also use not in and not EXISTS, but you should pay attention to null at this time.

Use the aggregate function in the case function

Suppose you have one of the following tables

School Number (STD_ID) Course ID (class_id) course name (class_name) Major in Flag (MAIN_CLASS_FLG)

100 1 Economics y

100 2 History N

200 2 History N

200 3 Archaeological Y

200 4 Computer N

300 4 Computer N

400 5 Chemical N

500 6 Mathematics N

Some students choose to take several courses at the same time (100,200) and some students only choose one course (300,400,500). Students taking multiple courses are asked to choose a course as their major, majoring in flag to write Y. Students who choose only one course, majoring in flag n (in fact, if you write Y, there is no trouble below, in order to give an example, also please include).

Now we're going to query this table in terms of the following two conditions

The person who took only one course returned the ID of the course.

People who take multiple courses, return the selected main course ID

The simple idea is to execute two different SQL statements for querying.

Conditions

Copy Code code as follows:

--Condition: A student who has chosen only one course

SELECT std_id, MAX (class_id) as Main_class

From StudentClass

GROUP by std_id

Having COUNT (*) = 1;

Execution results

std_id Main_class

------   ----------

300 4

400 5

500 6

Conditions

Copy Code code as follows:

--Conditions: Students who choose multiple subjects

SELECT std_id, class_id as Main_class

From StudentClass

WHERE main_class_flg = ' Y ';

Execution results

std_id Main_class

------  ----------

100 1

200 3

If you use the case function, we can solve the problem with just one SQL statement, as shown below

Copy Code code as follows:

SELECT std_id,

Case when COUNT (*) = 1-The situation of a student who chooses only one course

THEN MAX (class_id)

ELSE MAX (case when main_class_flg = ' Y '

THEN class_id

ELSE NULL End

)

End as Main_class

From StudentClass

GROUP by std_id;

Run results

std_id Main_class

------   ----------

100 1

200 3

300 4

400 5

500 6

By nesting the case function in the case function and using the case function in the aggregate function, we can solve this problem easily. Using the case function gives us greater freedom.

Finally, remind the novice using the case function to be careful not to make the following error

Copy Code code as follows:

Case Col_1

When 1 THEN ' right '

When NULL THEN ' wrong '

End

When NULL is always returned to unknown in this statement, wrong is never present. Because this sentence can be replaced by when col_1 = NULL, this is a wrong use, this time we should choose to use when Col_1 is NULL.