DB2 common table expressions (use of with statements)

---- Start

Speaking of the with statement, most people think it is used for Recursive queries except those who first heard of the with statement. In fact, it is only one of its purposes. Its original name is called:Common table expression)Literally, what do you think it is? In fact, it is used to defineTemporary set. Ah?Values statementIsn't it used to define a temporary set? How can the with statement also be used to define a temporary set? What do they have?DifferencesWhat about it?

 

VAlues statementYesClear ValueTo define a temporary set, as follows:

Values (1, 2), (1, 3), (2, 1) 

With statementYesQuery(That is, the SELECT statement) to define a temporary set. From this perspective, it is a bit like a view, but it is not a view. Do not misunderstand it. As follows:

Create Table user (<br/> name varchar (20) not null, --- name <br/> sex integer, --- gender (1, male 2, female) <br/> birthday date --- birthday <br/> );

With test (name_test, bday_test) as <br/> (<br/> select name, birthday from user -- Statement 1 <br/>) <br/> select name_test from test where bday_test = '2017-10-1 '; -- Statement 2 

Next, let's explain. FirstStatement 1Run the command to generate a result set with two columns (name, birthday). Then, we name the result set as test and rename the column name as name_test and bday_test; finally, executeStatement 2,From this temporary collection, we can find that the birthday is, that is, the same age in the Republic.

 

How is it? If you do not understand the statements, analyze them carefully. Let's take a lookVAlues and with statementsAn example is as follows:

With test (name_test, bday_test) as <br/> (<br/> values ('zhang san', '2017-7-1 '), ('Li si ', '2017-10-1 ') <br/> select name_test from test where bday_test = '2017-10-1'

From the above introduction, we can guess that the with statement is not familiar to most people. The with statement is designed for complex queries. It is indeed true. Here is a complex example, never miss out if you want to improve your technology. Consider the following:

Create Table user <br/> (<br/> name varchar (20) not null, -- name <br/> degree integer not null, -- education level (1, Junior College 2, undergraduate 3, Master 4, PhD) <br/> startworkdate date not null, -- entry time <br/> salary1 float not null, -- Basic Salary <br/> salary2 float not null -- bonus <br/> ); 

Assume that you want to query those questions: 1. Master's degree or doctor's degree. 2. The same degree and year of employment are the same, but the salary (basic salary + bonus) is equalAverage salary of employees under the same conditionsLow employees. (Haha, you may want to raise your salary). Do you understand the problem? How can I query it? We think like this:

 

1. query information obtained by employees who have a master's or doctor's degree.Result set 1,As follows:

Select name, degree, year (startworkdate) as worddate, salary1 + salary2 as salary from user where degree in (3, 4 ); 

2. Calculate the average salary based on the educational qualifications and the year of employment.Result set 2, As follows:

Select degree, year (startworkdate) as worddate, AVG (salary1 + salary2) as avg_salary <br/> from user where degree in (3, 4) <br/> group by degree, year (startworkdate) 

3.Education levelAndEmployment yearIsCondition UnionTwo result sets,Find wage <average wageThe following is a complete SQL statement:

With temp1 (name, degree, worddate, salary) as <br/> (<br/> select name, degree, year (startworkdate) as worddate, salary1 + salary2 as salary from user where degree in (3, 4) <br/>), <br/> temp2 (degree, worddate, avg_salary) as <br/> (<br/> select degree, year (startworkdate) as worddate, AVG (salary1 + salary2) as avg_salary <br/> from user where degree in (3, 4) <br/> group by degree, year (startworkdate) <br/> select name from temp1, temp2 where <br/> temp1.degree = temp2.degree <br/> and temp1.worddate = temp2.worddate <br/> and salary <avg_salary; 

The query results are completely correct, but we still haveSpace for improvement, In the queryResult set 2We retrieve data from the user table. In fact, the result set 1 has been queried.Result set 1You can get result set 2 through grouping, instead of getting result set 2 from the tables in the ER. By comparing the preceding and following statements, you can see what I mean!

With temp1 (name, degree, worddate, salary) as <br/> (<br/> select name, degree, year (startworkdate) as worddate, salary1 + salary2 as salary from user where degree in (3, 4) <br/>), <br/> temp2 (degree, worddate, avg_salary) as <br/> (<br/> select degree, worddate, AVG (salary) as avg_salary <br/> from temp1 <br/> group by degree, worddate <br/>) <br/> select name from temp1, temp2 where <br/> temp1.degree = temp2.degree <br/> and temp1.worddate = temp2.worddate <br/> and salary <avg_salary; 

Some friends may say that I can find it without using the with statement, as shown in the following code:

Select U. name from user as U, <br/> (<br/> select degree, year (startworkdate) as worddate, AVG (salary1 + salary2) as avg_salary <br/> from user where degree in (3,4) <br/> group by degree, year (startworkdate) <br/>) as G <br/> where u. degree = G. degree <br/> and year (U. startworkdate) = g. worddate <br/> and (salary1 + salary2) <G. avg_salary;

What is the difference between using with and not using? In general, the two methods do not have much difference in performance. However,

1. When the user table has many records

2. The ratio of master's degree or doctor (degree in (3, 4) in the User table is very small.

 

When the above conditions are met, the performance differences between the two statements will be apparent. Why? Because do not use with statementsThe User table is accessed twice.If the degree field has no index, the performance difference will be very obvious.

 

When you see this, if you have a good understanding of the above content, I believe you will have some experience with the with statement. However, the with statement can do more than this. The following describes how to use the with statementRecursive query. A typical example of recursive queries isTree Structure tableFor query, consider the following situations:

Forum homepage <br/> -- database development <br/> ---- DB2 <br/> ------ DB2 Article 1 <br/> -------- comment 1 of DB2 Article 1 <br/> -------- DB2 article 1 comment 2 <br/> ------ DB2 Article 2 <br/> ---- Oracle <br/> -- Java Technology 

The above is a typical example of a Forum. Next we will create a new table to store the above information.

Create Table BBS <br/> (<br/> parentid integer not null, <br/> ID integer not null, <br/> name varchar (200) not null --- sections, articles, comments, etc. <Br/>); <br/> insert into BBS (parentid, ID, name) values <br/> (0, 0, 'Forum homepage '), <br/> (11,111, 'database developer'), <br/> (, 'db2 '), <br/> (, 'db2 Article 1 '), <br/> (111,1111, 'comments 1 of DB2 Article 1 '), <br/> (111,1112, 'comments 2 of DB2 Article 1 '), <br/> (11,112, 'db2 Article 2'), <br/> (, 'oracle '), <br/> (, 'java techno '); 

Now everything is ready. Let's start querying. Suppose you wantQueryAll comments in 'db2 Article 1', some people say, This is not simple, as shown below.

Select * from BBS where parentid = (select ID from BBS where name = 'db2 '); 

The answer is completely correct. Now let youQueryWhat should I do with all the articles and comments of DB2? The traditional method is difficult to query, and recursive queries are useful at this time, as shown below:

With temp (parentid, ID, name) as <br/> (<br/> select parentid, ID, name from BBS where name = 'db2 '--- Statement 1 <br/> Union all --- Statement 2 <br/> select B. parentid, B. ID, B. name from BBS as B, temp as t where B. parentid = T. id --- Statement 3 <br/>) <br/> select name from temp; --- Statement 4 

After running, we found that the results were completely correct. How did it run? The following is a detailed explanation.

1. First,Statement 1Will be executed,ItRun only once,Cycle start point. Result set: DB2

2. NextLoop executionStatement 3Here, we need to introduce it in detail.

FirstIntention of Statement 3What is it? To put it bluntly, it isFind the next level of result set (DB2) produced by Statement 1So what is the next level of DB2 in the directory tree? It is 'db2 Article 1' and 'db2 Article 2', and the query result set is usedStart Point of the next cycleAnd then query their next level until there is no next level.

How is it? Not clear yet? Haha, it doesn't matter. Let's do it step by step:

First,Result set generated by Statement 1: DB2, as the starting point of the loop, associates it with the BBS table to find its next level. The query result is: 'db2 Article 1' and 'db2 Article 2'

NextLast Query Result(That is, 'db2 Article 1' and 'db2 Article 2') join the BBS table to find their next level. The query result is: 'db2 Article 1 comment 1' and 'db2 Article 1 comment 2 '.

ThenLast Query Result(That is, 'db2 Article 1 comment 1' and 'db2 Article 1 comment 2') are associated with the BBS table to find their next level. At this time, no result is returned,Loop ends.

3. The third step is to executeStatement 2,Put all the result sets together and finally get the temp result set.

4. Finally, we useStatement 4 Obtain the expected query result from the temp temporary set.

How can this problem be solved? If I haven't understood it, I can't do anything about it. Please note that

1,Be sure to noteJoin condition of Statement 3,Otherwise, it is easy to writeEndless loop.

2. Statement 2 must be union all

Finally, let's assume thatWhere clause of Statement 1What kind of results will be generated when I remove them?

--- For more information, see:DB2 SQL

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

---- Last updated at 2010.1.27

---- Written by wave at 2009.9.27

---- End