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

This article is to share with you about how to use case sentences in sql. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

How to use Case in SQL

Case comes in two formats. Simple Case function and Case search function. The copy code is as follows:-- simple Case function

CASE sex

WHEN'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

These two ways can achieve the same function. The writing of the simple Case function is relatively simple, but compared with the Case search function, there are some functional limitations, such as writing judgment.

One more thing to note is that the Case function only returns the first qualified value, and the rest of the Case will be automatically ignored.

-- for example, in the following SQL, you can never get the "second type" result. The copied code is as follows: CASE WHEN col_1 IN ('averse,' b') THEN 'first'

WHEN col_1 IN ('a') THEN 'category II'

ELSE' other 'END

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

First, the known data are grouped and analyzed in another way.

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

National (country) population (population)

China 600

United States 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

Other 700

What will you do if you want to solve this problem? Generating a View with a state Code is a solution, but it is difficult to change the statistical method dynamically.

If you use the Case function, the SQL code is as follows: the copy code is as follows: SELECT SUM (population)

CASE country

WHEN 'China' THEN 'Asia'

WHEN 'India' THEN 'Asia'

WHEN 'Japan' THEN 'Asia'

WHEN 'USA' 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 'USA' THEN 'North America'

WHEN 'Canada' THEN 'North America'

WHEN 'Mexico' THEN 'North America'

ELSE 'other' END

Similarly, we can also use this method to determine the level of wages and count the number of people in each grade. The SQL code is as follows; the copy code is as follows: SELECT

CASE WHEN salary 500 AND salary 600 AND salary 800 AND salary 1000)

If the conditions of the female staff are met, the male staff will not be able to enter.

Fourth, there are selective UPDATE according to the conditions.

For example, there are the following update conditions

For employees whose salary is above, the salary will be reduced by%.

For employees whose wages are in the range, the salary will be increased by%.

It is easy to consider choosing to execute the UPDATE statement twice, and the copy code is as follows:-- condition

UPDATE Personnel

SET salary = salary * 0.9

WHERE salary > = 5000

-- conditions

UPDATE Personnel

SET salary = salary * 1.15

WHERE salary > = 2000 AND salary

< 4600; 但是事情没有想象得那么简单，假设有个人工资块。首先，按照条件，工资减少%，变成工资。接下来运行第二个SQL时候，因为这个人的工资是在到的范围之内，需增加%，最后这个人的工资结果是,不但没有减少，反而增加了。如果要是反过来执行，那么工资的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:复制代码 代码如下: UPDATE Personnel SET salary = CASE WHEN salary >

= 5000

THEN salary * 0.9

WHEN salary > = 2000 AND salary < 4600

THEN salary * 1.15

ELSE salary END

It should be noted here that the ELSE salary of the last line is required. Without this line, the salary of people who do not meet these two conditions will be written as NUll, which is not good. It is important to note that the default value for the Else part of the Case function is NULL.

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

In general, if you want to exchange Primary key,an and b of two pieces of data, you need to temporarily store, copy, and read back the data. If you use the Case function, everything becomes much easier.

P_key col_1 col_2

A 1 Zhang San

B 2 Li Si

C 3 Wang Wu

Assuming that as with the data above, you need to exchange the primary keys an and b with each other. If you use the Case function to implement it, the code is copied 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 ('averse,' b')

Similarly, you can exchange two Unique key. It should be noted that if there is a need to exchange primary keys, it is most likely that the design of the table is not in place. It is recommended to check whether the design of the table is appropriate.

Fifth, check whether the data of the two tables are consistent.

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, with IN,EXISTS, you can perform subqueries to achieve more functionality.

As an example, there are two tables, tbl_A,tbl_B, with keyCol columns in both tables. Now let's compare the two tables. If the data of the keyCol column in tbl_A can be found in the data of the keyCol column of tbl_B, the result is returned: 'Matched',' if not found, return the result 'Unmatched'.

To do this, you can copy the code using the following two statements:-- 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 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 pay attention to NULL at this point.

Sixth, use the aggregate function in the Case function

Suppose you have the following table

Std_id course ID (class_id) course name (class_name) majoring in flag (main_class_flg)

100 1 Economics Y

100 2 History N

200 2 History N

200 3 Archaeology Y

200 4 computer N

300 4 computer N

400 5 Chemical N

500 6 Mathematical N

Some students choose to take several courses at the same time (100200), while others choose only one course (300400500). Students who take multiple courses should choose a course as their major, which is written in Y in flag. For students who choose only one course, their major flag is N (in fact, if you write Y, there will be no trouble below, and please include more for example).

Now we are going to query this table according to the following two criteria

Those who take only one course will return to the ID of that course.

For those who take more than one course, return to the selected main course ID

The simple idea is to execute two different SQL statements to query.

The conditional copy code is as follows:-- condition: students who have chosen only one course

SELECT std_id, MAX (class_id) AS main_class

FROM Studentclass

GROUP BY std_id

HAVING COUNT (*) = 1

Execution result

STD_ID MAIN_class

--

300 4

400 5

500 6

The conditional copy code is as follows:-- condition: students who choose multiple courses

SELECT std_id, class_id AS main_class

FROM Studentclass

WHERE main_class_flg ='Y'

Execution result

STD_ID MAIN_class

--

100 1

200 3

If we use the Case function, we only need a SQL statement to solve the problem, as shown below. The copy code is as follows: SELECT std_id

CASE WHEN COUNT (*) = 1-the case of students who have chosen 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

Running result

STD_ID MAIN_class

--

100 1

200 3

300 4

400 5

500 6

By nesting Case functions in Case functions and using Case functions in aggregate functions, we can easily solve this problem. The use of the Case function gives us more freedom.

Finally, remind novice users of the Case function not to make the following error to copy the code as follows: CASE col_1

WHEN 1 THEN 'Right'

WHEN NULL THEN 'Wrong'

END

The line When Null always returns unknown in this statement, so the case of Wrong never occurs. Because this sentence can be replaced with WHEN col_1 = NULL, which is a wrong usage, we should choose to use WHEN col_1 IS NULL at this time.

The above is how to use the case statement in sql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.