Define a temporary set (use of values statements)

---- Start

MentionedValues statement, Many people feel very unfamiliar. Haha, you will suddenly realize when you see the following statements.

Insert into user (name, birthday) values ('zhang san', '2017-1-1 '); 

Oh, it turns out that this is the values statement. That's right. What is the purpose of the legendary values statement? Just like the title of our article, it is used to define a temporary set.

Let's take a look at several simple values statements as follows:

Values 1 -- 1 row and 1 column <br/> values 1, 2 -- 1 row and 2 columns <br/> values (1), (2) -- 2 rows and 1 column <br/> values (), (), () -- 3 rows and 2 columns 

Is it a bit messy, so we will take it as a normal SQL, is it very intuitive (Do not think it can only be viewed. It can be executed.). Let's guess which rows and columns of the following statement are?

Values (1), (2) <br/> values (1, 2), (1, 3), (2, 1 )) 

Run the command to see if it is the same as what you think. As we have said before, the values statement defines a temporary set, and we know that the set can be sorted and grouped. Can the values statement be used? You can try the following statement:

--- Sort <br/> select * from <br/> (<br/> values (1, 2), (2, 1) <br/>) as temp <br/> order by 1 DESC <br/> --- group <br/> select a, count (*) from <br/> (<br/> values (1, 2), (1, 3), (2, 1) <br/>) as temp (A, B) <br/> group by 

You should have learned how to define the values statement. You may also want to know, in the actual environment, under which circumstances should we use the values statement? The answer may disappoint you. The answer is that any temporary table is needed. For example, consider the following situation:

Create Table user <br/> (<br/> name varchar (20) not null, --- name <br/> Department integer, --- Department (1. Marketing Department 2. Management Department 3. R & D department) <br/> birthday date --- birthday <br/> ); 

Now, we have the following conditions for you to check your name:

Department birthday

Marketing Department 1949-10-1

Department of Management

R & D department 1997-7-1

......

There are many such conditions, and we will give an example of the above three conditions. What should I do? Some may write as follows:

Select * from user where department in (1949, 3) and birthday in ('2017-10-1 ', '2017-12-18', '2017-7-1 '); 

The query results are incorrect, because the management department and birthday are also found at 1949-10-1. Since this cannot be done, some people may write it like this:

Select * from user where (department, birthday) in <br/> (1, '2017-10-1 '), <br/> (2, '2017-12-18 '), <br/> (3, '2017-7-1') <br/> ); 

The result shows that this statement cannot be executed at all. Some people may say that there is no way to execute it one by one, as shown below:

Select * from user where Department = 1 and Birthday = '2017-10-1 '; <br/> select * from user where Department = 2 and Birthday = '2017-12-18 '; <br/> select * from user where Department = 3 and Birthday = '2017-7-1 '; 

After a long wait (because of the poor efficiency), we finally found out why we still don't want anything, such as line breaks or even some messages printed by DB2. Based on the above disadvantages, smart people come up with a good way to create a new table (such as temp), import the above conditions, and then query them. Isn't that all right? As follows:

Create Table temp <br/> (<br/> Department integer, <br/> birthday date <br/> ); 

Then, import the conditions to the temporary table and query the conditions as follows:

Select * from user where (department, birthday) in <br/> (<br/> select Department, birthday from temp <br/> ); 

Except for the trouble, everything seems perfect. However, people who know the values statement will say: It is too troublesome to define a persistent table. You can define a temporary set with values, as shown below:

Select * from user where (department, birthday) in <br/> (<br/> values (1, '2017-10-1 '), (2, '2017-12-18 '), (3, '2017-7-1') <br/> ); 

At this point, we feel that it is very easy to do this, but not necessarily, there is a simpler method, as shown below:

Select * from user where Department = 1 and Birthday = '2017-10-1 '<br/> Union <br/> select * from user where Department = 2 and Birthday = '2017-12-18 '<br/> Union <br/> select * from user where Department = 3 and Birthday = '2017-7-1' 

When you see this, this article should be over. What are your inspirations?

 

--- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2009.9.27

---- Written by shangbo on 2009.9.25

---- End