with definition and usage
-------Part of the content is reproduced and processed by finishing---------------------1.with Understanding and basic usage the WITH statement, in addition to those who first heard of the WITH statement, most people think it is used for recursive query. In fact, it is only a use of it, its real name as we have inscribed, called: Public table expression (Common table expression), from the literal understanding of what do you think it is for? In fact, it is used to define a temporary collection. Ah? Is the values statement used to define a temporary collection? How can a with statement also be used to define a temporary collection? What difference do they have?
The values statement defines the temporary collection with an explicit value, as follows: Values (1,3), (2,1) with statements that define temporary collections with a query (that is, a SELECT statement), from this perspective, a bit like a view, but not a view, we must not misunderstand. For example the following:
--Build the drop table USER; CREATE TABLE USER (name VARCHAR () not NULL,---nameSex INTEGER,---gender (1, male2, female)BIRTHDAY DATE---Birthday);--interpolate data insert into user (Name,sex,birthday) VALUES (' Zhangshan ', ' 1 ', ' 1990-1-1 '); insert into user (Name,sex,birthday) VALUES (' Lisi ', ' 2 ', ' 1991-1-1 '); insert into user (Name,sex,birthday) VALUES (' Wangwu ', ' 1 ', ' 1992-1-1 '); INSERT INTO User ( Name,sex,birthday) VALUES (' Sunliu ', ' 2 ', ' 1949-10-1 '); insert into user (Name,sex,birthday) VALUES (' Tianqi ', ' 1 ', ' 1994-1-1 '); insert into user (Name,sex,birthday) VALUES (' Zhaoba ', ' 2 ', ' 1995-1-1 ');
With TEST (Name_test, bday_test) as--test is named after the result set that is queried in parentheses, followed by the renamed column (SELECT Name,birthday from user--statement 1)SELECT name_test from TEST WHERE bday_test= ' 1949-10-1 ';--Statement 2
Let's explain that first statement 1 executes, it produces a result set with two columns (name,birthday), then we name the result set as TEST and rename the column name to Name_test, Bday_test; Finally we execute Statement 2, The birthday from this temporary collection was 1949-10-1, the age of the Republic.
What do you think? If you don't feel well, please analyze the above statement carefully. Here's an example of using the values statement with the WITH statement, as follows:
With TEST (Name_test, bday_test) as(VALUES (' Zhang San ', ' 1997-7-1 '), (' John Doe ', ' 1949-10-1 '))SELECT name_test from TEST WHERE bday_test= ' 1949-10-1 '
From the above introduction and with the statement is not familiar to most people can guess, with the statement is designed for complex queries, it is true, below we give a complex example, want to improve the technology of friends can not be missed. Consider the following scenario:
--Build table drop tables USER2; CREATE TABLE USER2(Name VARCHAR () not NULL,--nameDegree INTEGER not NULL,--education (1, Specialty 2, undergraduate 3, Master 4, PhD)Startworkdate date not NULL,--entry timeSALARY1 FLOAT not NULL,--base salarySALARY2 FLOAT not null--bonus);
--interpolation data insert into User2 (Name,degree,startworkdate,salary1,salary2) VALUES (' Zhangsan ', 1, ' 1995-1-1 ', 10000.00,1600.00 Insert into User2 (Name,degree,startworkdate,salary1,salary2) VALUES (' Lisi ', 2, ' 1996-1-1 ', 5000.00,1500.00); insert into User2 (Name,degree,startworkdate,salary1,salary2) VALUES (' Wangwu ', 3, ' 1997-1-1 ', 6000.00,1400.00); INSERT INTO User2 (Name,degree,startworkdate,salary1,salary2) VALUES (' Sunliu ', 4, ' 1998-1-1 ', 7000.00,1300.00); insert INTO User2 ( Name,degree,startworkdate,salary1,salary2) VALUES (' Tianqi ', 2, ' 1999-1-1 ', ' 7000 ', ' 1300 '); INSERT into User2 (name, Degree,startworkdate,salary1,salary2) VALUES (' Zhaoba ', 1, ' 2000-1-1 ', 9000,1400); insert into User2 (Name,degree, Startworkdate,salary1,salary2) VALUES (' Qianjiu ', 3, ' 1997-1-1 ', 2000,1000); insert into User2 (Name,degree, Startworkdate,salary1,salary2) VALUES (' Dushe ', 4, ' 1992-1-1 ', 3000,1000); select * from User2;
Let's say you have a look at those 1, education is a master's or PhD2, the same degree, the entry year is the same, but the salary (base salary + bonus) is lower than the average salary of employees of the same conditions. Haha, may be to raise wages), do not know you understand the question? How to query it? That's what we think:
1. Those employees who inquire about the degree of their master's or doctor's degrees get the result set 1, as follows: SELECT Name,degree,year (startworkdate) as worddate,salary1+salary2 as SALARY from USER2 WHERE degree in (3,4);
2, according to the education and the entry year group, the average wage to get the result set 2, as follows: SELECT Degree,year (startworkdate) as Worddate, AVG (Salary1+salary2) as Avg_salaryFrom USER2 WHERE degree in (3,4)GROUP by Degree,year (startworkdate);
3. To combine two result sets with qualifications and entry years to find employees with wages < average salary, the following is the complete Sql:with TEMP1 (name,degree,worddate,salary) as(SELECT Name,degree,year (startworkdate) as Worddate, Salary1+salary2 as SALARY from USER2 WHERE degree in (3,4)),TEMP2 (Degree,worddate,avg_salary) as(SELECT Degree,year (startworkdate) as Worddate, AVG (Salary1+salary2) as Avg_salaryFrom USER2 WHERE degree in (3,4)GROUP by Degree,year (startworkdate))SELECT NAME from TEMP1, TEMP2 WHERETEMP1. Degree=temp2. Degreeand TEMP1. Worddate=temp2. Worddateand salary<avg_salary;
The query results are completely correct, but we still have room for improvement, and in query result set 2 we get the data from the user table. In fact, at this time the result set 1 has been queried, we can completely from the result set 1 by grouping to get the result set 2, instead of getting the result set 2 from the Uer table, compare the above and below the statement you can know what I mean!With TEMP1 (name,degree,worddate,salary) as(SELECT Name,degree,year (startworkdate) as Worddate, Salary1+salary2 as SALARY from USER2 WHERE degree in (3,4)),TEMP2 (Degree,worddate,avg_salary) as(SELECT degree,worddate, AVG (SALARY) as Avg_salaryFrom TEMP1GROUP by Degree,worddate)SELECT NAME from TEMP1, TEMP2 WHERETEMP1. Degree=temp2. Degreeand TEMP1. Worddate=temp2. Worddateand salary<avg_salary;Maybe some friends will say, I do not have to use with the statement can be found out, it is true, as follows:SELECT u.name from USER2 as U, (SELECT Degree,year (startworkdate) as Worddate, AVG (Salary1+salary2) as Avg_salaryfrom USER2 WHERE degree in (3,4)GROUP by Degree,year (startworkdate)) as GWHERE U.degree=g.degreeand year (u.startworkdate) =g.worddateand (Salary1+salary2) <G.AVG_SALARY;
What is the difference between the two ways of using with and not using with? In general, these two types of writing do not differ significantly in performance, but, 1, when the user table records a lot of 2, Master's or PhD (degree in (3,4)) in the user table is very small proportion
When the above conditions are met, the difference in performance between the two formulations will be revealed, for what? Because the user table is accessed 2 times without the use of the with notation, the performance difference will be noticeable if the degree field is not indexed.
Recursive application of 2.with one when you see this, if you understand the above, I believe you will have some experience with the statement. However, with the statement can do more than these, the following to introduce you, how to use with the statement to do recursive query. A typical example of a recursive query is to query the table of a tree structure and consider the following:
01. Forum Homepage02.--Database Development----DB2.------DB2 Article 1--------comments on DB2 article 1 1--------DB2 Article 1 's comments 2------DB2 Article 2----Oracle09.--java TechnologyThe above is a typical example of a forum, below we create a new table to store the above information. drop table BBS; CREATE TABLE BBS(ParentID INTEGER not NULL,ID INTEGER not NULL,NAME VARCHAR ($) Not NULL---plates, articles, comments, and so on.);INSERT INTO BBS (parentid,id,name) values(0,0, ' Forum homepage '),(0,1, ' database development '),(1,11, ' DB2 '),(11,111, ' DB2 article 1 '),(111,1111, ' DB2 article 1 of Comments 1 '),(111,1112, ' DB2 article 1 of Comments 2 '),(11,112, ' DB2 Article 2 '),(1,12, ' Oracle '),(0,2, ' Java technology ');Now that everything is all right, let's start inquiring. Let's say now that you're looking at all the comments on ' DB2 article 1 ', someone says, it's not as simple as this.SELECT * from the BBS where parentid= (SELECT ID from BBS where name= ' DB2 article 1 ');The answer is absolutely right. Now, let's ask you to check all the articles and comments of DB2, what should I do? The traditional method is difficult to query, this time the recursive query comes in handy, as follows:With TEMP (Parentid,id,name) as(SELECT parentid,id,name from BBS WHERE name= ' DB2 '---statement 1UNION All---Statement 2SELECT b.parentid,b.id,b.name from BBS as B, TEMP as T WHERE b.parentid=t.id---statement 3)SELECT NAME from TEMP; ---statement 4
The first SELECT statement within a WITH clause is the initialization table. It executes only once. Its result forms the initial contents of the virtual table as a seed of recursion. In the example above, the seed is ' NAME ' forOne or more lines of DB2.
The second SELECT statement executes multiple times. Passes a seed as input to the second SELECT statement to produce the next rowset. Adds the result (UNION all) to the current contents of the virtual table and puts it back in to form the input for the next pass. As long as there is a line, the process will continue.
After running, we find that the results are completely correct, and how exactly does it work? Let us explain in detail below. 1, first, statement 1 will be executed, it executes only once, as the starting point of the loop. Get result set: DB22, Next, will loop execution Statement 3, here we need to introduce in detail. What is the intention of statement 3 first? Plainly, it is the next level of finding statement 1 to produce the result set (DB2), so what is the next level of DB2 in the directory tree? Are ' DB2 article 1 ' and ' DB2 article 2 ', and query the result set as the starting point for the next cycle, and then query their next level until there is no next level.
What do you think? Don't you get it? Haha, it doesn't matter, we step by step: First, Statement 1 produces the result set: DB2, as the starting point of the loop, it and BBS Table Association to find its next level, the query results are: ' DB2 article 1 ' and ' DB2 article 2 ' Then, the last query results (that is, ' DB2 article 1 ' and ' DB2 Article 2 ') and BBS tables are associated to find their next level, after query results are: ' DB2 article 1 of comments 1 ' and ' DB2 article 1 of comments 2 '. Then, in the last query results (that is, ' DB2 article 1 of comments 1 ' and ' DB2 article 1 of Comments 2 ') and BBS tables to find their next level, at this time, no results returned, the loop ends. 3, third, the statement 2 will be executed, all the result sets are put together, finally get the temp result set. 4. Finally, we get the query result we expect from the temp temporary collection through statement 4.
A special reminder is 1, be sure to pay attention to statement 3 of the association conditions, otherwise it is easy to write a dead loop. 2. Statement 2 must be UNION all finally, let's guess what the result will be if you remove the WHERE clause of statement 1. Removing where will be all dead loops, since each result set is a full-check record and is always.
3.with recursive application two, row-to-column--1. Build tables drop table Zxt_testcreate table Zxt_test(ID varchar (10),Ivalue varchar (20),Iname varchar (20)); Commit;select * from Zxt_test;--------------2. Inserting test statementsINSERT into zxt_test values (' 1 ', ' AA ', ' X '), (' 2 ', ' BB ', ' X '), (' 3 ', ' BB ', ' X '), (' 1 ', ' BB ', ' Y '), (' 2 ', ' BB ', ' Y '), (' 3 ', ' BB ', ' Y ');Commit
WithS as (--here is the sort by using Iname to partition, ID. If the table does not have such a distinct ID field, you can use RowNum () to generate the ordinal numberSelect Row_number () over (partition by iname order by ID) ID1,Row_number () over (partition by iname order by ID) ID2,Ivalue,iname from Zxt_test),T (Iname,id1,id2,ivalue) as(Select Iname,id1,id2,cast (ivalue as varchar) froms where Id1 =1 and id2=1--statement 1UNION ALLSelect T.iname,t.id1+1,t.id2,cast (s.ivalue| | ', ' | | T.ivalue as varchar (100))--Statement 2fromS, twhere s.id2=t.id1+1 and t.iname = S.iname)--where S.iname = T.iname can be removed without affectingSelect Iname,ivalue from-t where t.id1= (select Max (ID1) from S where s.iname = T.iname); --Statement 3Result set S
Inside the temp table T, first EXECUTE statement 1. Gets the root result set, which is the basis of the loop. NOTE: Statement 1 executes only once.
The first loop passed in T to Statement 2 Gets the result set to be:At this point the federated result set temp table T is: (V is the value after the bean number)InameT.id1+1T.id2VInameT.id1T.id2VX2 1Bb,aa X1 1AaY2 1BB,BB Y 1 1BbX2 1Bb,aay2 1Bb,bb
The second loop passed in T to Statement 2 Gets the result set to be:At this point the Union's final result set temp table T is: inameT.id1+1T.id2VInameT.id1T.id2VX3 1Bb,bb,aa X1 1AaY3 1BB,BB,BB Y1 1BbX2 1Bb,aay2 1Bb,bbx 3 1Bb,bb,aay3 1BB,BB,BB in statement 3 plus conditional on the final result set in temporary table T id1 the highest value of the record, resulting in the final expected row-to-column result set:
ExampleDB2 Row to column: (not sure how many rows of the case) to realize the idea, first recursion, then sort, take the first line. With RS as (select Bbd043,row_number () + () RN from bb72 where bae007= ' 10001 '), RPL (rn,bbd043) as(Select ROOT. Rn,cast (root.bbd043 as varchar) from Rs ROOTUNION AllSELECT Child. rn,child.bbd043| | ', ' | | parent.bbd043 fromRPL Parent,rs Child WHEREPARENT. Rn+1=child. Rn)SELECT MAX (bbd043) bbd043 from RPLGROUP by RN ORDER by RN DESC-FETCH first 1 ROWS only;
Comparison of the efficiency of row DB2
high efficiency to cope with large data volumesWithS AS (Select Row_number () over () ID1, Row_number () over () Id2,AAE004 from BB20 where AAE004 <> '------sql01),T (id1,id2,aae004) as(Select id1,id2,aae004 froms where Id1 =1 and id2=1UNION ALLSelect T.id1+1,t.id2,cast (s.aae004| | ', ' | | t.aae004 as varchar (20000))FromS, twhere s.id2=t.id1+1)Select AAE004 from T where t.id1= (select Max (ID1) from S);
poor efficiency, a large amount of data, it's a dick.With RS as (select Aae004,row_number () + () RN from BB20 where AAE004 <> "------sql02", RPL (rn,aae004) as(Select ROOT. Rn,cast (ROOT. AAE004 as varchar (20000)) from Rs ROOTUNION AllSELECT Child. Rn,child. aae004| | ', ' | | PARENT. AAE004 from RPL Parent,rs Child WHEREPARENT. Rn+1=child. Rn)SELECT RPL. Rn,max (AAE004) AAE004 from RPL GROUP by RN ORDER by RN DESC-FETCH first 1 ROWS only;
Definition and usage of DB2 with
