Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the CASE expression in SQL, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

First of all, let's learn the basic writing. CASE expressions have simple CASE expressions.

(simple case expression) and search for CASE expressions (searched case)

Expression) are written in two ways, as shown below.

The execution result of the two kinds of writing is the same, if the "sex" column (field) is'1', then

The result is male; if it is'2', then the result is female. A simple CASE expression is just like its

The name is simple to write, but the things that can be realized are relatively limited.

When we write SQL statements, we need to be aware that when we find that the WHEN clause is true

The determination of the true or false value of the CASE expression is aborted, and the remaining WHEN clauses are

ignore. In order to avoid unnecessary confusion, you should pay attention to the condition when using the WHEN clause

Exclusiveness.

In addition, when using CASE expressions, you need to be aware of the following points.

Note 1: unify the data types returned by each branch

Although there is no need to say much on this point, it is important to emphasize here that you must pay attention to the CASE table.

Whether the data types returned by each branch in the formula are consistent. A branch returns a character type, while

It is incorrect to write the numerical type returned by other branches.

Note 2: don't forget to write END

When using CASE expressions, the most common syntax error is forgetting to write END. Although

However, it is not a fatal mistake to forget that the program will return an error message that is easier to understand when writing.

Mistake. However, I feel that I have no problem with writing, but most of the mistakes made in execution are caused by this.

It is caused by a number of reasons, so please pay attention to it.

Note 3: get into the habit of writing ELSE clauses

Unlike END, the ELSE clause is optional, and there is no error if it is not written. Do not write ELSE child

Sentence, the result of execution of the CASE expression is NULL. But not writing may lead to "grammar."

There is no mistake, but the result is not right. "this kind of trouble, which is not easy to trace the cause, so it is best to make it clear

Write the ELSE clause (even if the result can be NULL). Grow up like this

We can see clearly from the code that NULL will be generated under this condition

And errors can be reduced when the code is modified in the future

Convert the existing numbering mode to a new one and count

When conducting non-customized statistics, we often encounter the conversion of existing numbering methods to another

A need for easy analysis and statistics.

For example, there is now a picture according to "1: Hokkaido", "2: Aomori", …...,'47: Chong

Rope'"this numbering method to count the population of the prefecture of the capital, we need to use the northeast and customs.

East, Kyushu and other areas are grouped as units, and the population is counted. To be specific, it is unification.

Take into account the contents of the following table PopTbl, and get the results shown in the above table "Statistical results".

The key here is to copy the CASE expression in the SELECT clause to GROUP BY

Clause. It is important to note that if you GROUP the column "pref_name" before conversion

BY, you can't get the right result (because this doesn't cause syntax errors, so it's easy

Be ignored).

Similarly, the values can be classified according to the appropriate level. For example, by person

When querying the number of counties in Dudao Prefecture by pop_class, you can look like the following

Write the SQL statement like this.

This technique is very useful. However, it must be in the SELECT clause and the GROUP BY clause

It's a bit troublesome to write the same CASE expression in both places. When it needs to be modified later.

It's easy to make a mistake that changes only one place and forgets the other.

So, if we can write like this, it will be much more convenient.

Yes, the GROUP BY clause here uses exactly the column defined in the SELECT clause.

Another name-- district. But strictly speaking, this way of writing is against the rules of the standard SQL.

Then. Because the GROUP BY clause executes before the SELECT statement, in GROUP

References in the BY clause to aliases defined in the SELECT clause are not allowed. Fact

When this method is used in Oracle, DB2, SQL Server, and other databases, the

Wrong.

However, there are also databases that support such SQL statements, such as PostgreSQL and

In MySQL, this query statement can be executed smoothly. This is because these databases

When the query statement is executed, the list in the SELECT clause is scanned and the columns are scanned first

To do the calculations. However, since this is a violation of the standard, it is not highly recommended here.

Use. However, the SQL statement written in this way is indeed very concise and readable

Fine.

Statistics of different conditions with a SQL statement

Statistics of different conditions is one of the famous uses of CASE expressions. For example, we need

Add the "gender" column to the table PopTbl that stores the population of each county, and then ask for sex.

The number of people collected by the name of the county. Specifically, it is the data in the statistical table PopTbl2, but

After that, the results are obtained as shown in the table "Statistical results".

What the above code does is count the number of "men" (that is,'1') in each county.

And the number of "women" (i.e.'2'). In other words, this is the conversion of the "row structure" data.

It becomes the data of "column structure". In addition to SUM, aggregate functions such as COUNT and AVG can also be used.

Data used to convert row structure data into column structure data.

The valuable thing about this technique is that it can convert the query results of SQL into two-dimensional table.

Format. If you are simply aggregating with GROUP BY, the query must pass through the

An application such as the host language or Excel converts the format of the result to

Is a crosstab. If you look at the implementation results above, you will find that the output at this time is already a sidebar for the county.

A cross-table with a first name and a gender header. This function is very convenient when making statistical tables.

If you describe this technique in one sentence, you can say:

The novice uses the WHERE clause for conditional branching and the master uses the SELECT clause for conditional score.

Yes, please.

Conditional branching in a UPDATE statement

Consider this requirement: take the current value of a numeric column as the judgment object

Update it to a different value. The question here is how many conditions will be required for UPDATE operation at this time.

A branch. For example, we use the following table of salary information for employees in the personnel department of the company

Salaries, take a look at this situation.

Suppose you now need to update the data of the table according to the following conditions.

01. For employees with a current salary of more than 300,000 yen, the salary will be reduced by 10%.

02. For employees who currently earn more than 250,000 yen and are dissatisfied with 280000 yen, the salary will be increased.

20%.

At first glance, it seems possible to perform the following two UPDATE operations respectively, but this

But the result is incorrect.

Let's analyze the reasons for the incorrectness. For example, there is an employee here whose current salary is

300,000 yen, after performing the UPDATE operation according to condition 1, the salary will be updated to 270000

Yen, but after continuing to perform the UPDATE operation under condition 2, the salary will be updated to

It's 324,000 yen.

Such a result is certainly not what the personnel department wants. The salary of the employee Sada must be accurately reduced to

270,000 yen. The problem is that after the first UPDATE operation is executed, the "current salary" is paid.

If there is a change, if it is still regarded as the judgment condition of the second UPDATE, the result will not

Accurate. However, even if the execution order of the two SQL statements is reversed, the current salary

For employees with 270000 yen, there will also be problems with the results of their salary updates. To avoid this

Some questions, which accurately express the intention of the abominable Minister of personnel, can be used as follows

CASE expression to write SQL.

The ELSE salary of the last line of the SQL statement is very important and must

Write it down. Because without it, the wages of employees who do not meet both condition 1 and condition 2 will be

Is updated to NULL. This has something to do with the design of CASE expressions, which are introduced at the beginning

We already know from the time of the CASE expression that if the CASE expression is not specified

If the ELSE clause is specified, the result of execution will be processed as ELSE NULL by default. Now

As we all know, at the beginning, the author stressed that when using CASE expressions, we should habitually write ELSE.

The reason for the clause, right?

This technique has a wide range of applications. For example, you can simply use it to exchange the primary key value for the

A kind of heavy work. Usually, when we want to swap the primary key values an and b, we need to change the primary key value

Temporarily converted to some intermediate value. UPDATE operation needs to be performed 3 times when using this method.

Do, but if you use the CASE expression, you can do it once.

Obviously, this SQL statement is updated to b if it is a, and more so if it is b

UPDATE operations are performed for conditional branches such as "a".

One of the great advantages of CASE expressions is the ability to judge expressions. That is, in CASE expressions, we can use BETWEEN, LIKE, and

And IN and EXISTS predicates that can nest subqueries. Therefore, CASE expressions have a very powerful expressive power.

As shown below, here is a list of courses for qualified training schools and a monthly management sheet

A list of the courses offered.

We need to use these two tables to generate a crosstab like this, so that we can know at a glance

Courses offered every month.

What we need to do is to check that there is a table CourseMaster in each month in the table OpenCourses

Which courses are in. This matching condition can be written in CASE expressions.

Use IN

Use EXISTS

Such queries are not aggregated, so they do not need to be sorted, and are only fixed when the month increases.

Just change the SELECT clause, and the expansibility is better.

Whether you use IN or EXISTS, the result is the same, but in terms of performance

Say, EXISTS is better. Subqueries through EXISTS can use "month"

A primary key index like "course_id", so especially when the data in the table OpenCourses

It has more advantages when there are more times.

Using aggregate functions in CASE expressions

Next, let's introduce the slightly more advanced usage. At first glance, this usage may seem like

Grammatical errors are actually not the case. Let's look at an example, suppose there is one here.

Shows a list of students and the societies they join. As shown in the table StudentClub, this one

The primary key of the table is "student number, community ID", which stores the many-to-many relationship between students and societies.

Some students join multiple clubs at the same time (such as students with student numbers of 100 and 200), and some

Students join only one club (such as students with student numbers 300, 400, 500). For

For students who have joined multiple societies, we set their "main community logo" column to Y or

N to indicate which club is his main club; for those who have joined only one club

Yes, we set its "main community logo" column to N.

Next, we query the data in this table according to the following criteria.

01. Get the club ID of students who have joined only one club.

02. Get the main community ID of students who have joined more than one club.

It is easy to think of a way to write SQL statements for two conditions to query. If you want to

To know whether students have joined multiple clubs or not, we need to use the HAVING clause to aggregate

If you make a judgment.

You can also get the correct results by doing this, but you need to write multiple SQL statements. And if you use

CASE expression, the following SQL statement will be fine.

This SQL statement uses the aggregate function in the CASE expression, and in the aggregate function

The CASE expression is used. This kind of nesting writing is a bit dazzling, and its main purpose

It is CASE WHEN COUNT (*) = 1. ELSE... . Such an CASE expression

To express a conditional branch such as "have you joined only one club or multiple clubs?" I

When we first learned SQL, we all learned to use it when judging the condition of the aggregation result.

HAVING clause, but as you can see from this example, CASE is used in the SELECT statement

Expressions can do the same thing, which is a relatively new way of writing. If you use one sentence

To describe this technique, you can say:

The novice uses the HAVING clause for conditional branching and the master uses the SELECT clause for conditional score.

Yes, please.

Through this example, we can understand that when CASE expressions are used in SELECT clauses,

It can be written either inside or outside the aggregate function. This high degree of freedom

Is the charm of CASE expressions.

As an expression, the CASE expression is judged to be a fixed value when executed, so it

It can be written inside the aggregate function; and because it is an expression, it can also be written in the

SELECE clause, GROUP BY clause, WHERE clause, ORDER BY clause. Jane

On a single point, where you can write column names and constants, you can usually write CASE expressions.

Thank you for reading this article carefully. I hope the article "how to use CASE expressions in SQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.