DB2 building a temporary result set

One

VALUES (' 1 ', 2, 3) are one line ' 1 ' 2 3 row data types can be different values (' 1 ', 2,3), (' F ', 5,6) are two lines

(values) is a column

1

2

3

So the data type must be the same, or the error

Two with make temporary table, with can also query recursively

Two. 1 with can be combined with the SELECT statement to make a temporary table

CREATE TABLE USER(NAMEVARCHAR( -) not NULL,--nameDegreeINTEGER  not NULL,--Education (1, Specialty 2, undergraduate 3, Master 4, PhD)Startworkdate Date not NULL,--Time of entrySALARY1FLOAT  not NULL,--Base SalarySALARY2FLOAT  not NULL--Bonuses);

Let's say you have a look at those 1, the degree is a master or Doctor 2, the same degree, the entry year is the same, but the salary (base salary + bonus) is lower than the average wage of employees. (Haha, may be to raise wages), do not know you understand the question? How to query it?

There are two ways of comparing:

A

 withTEMP1 (Name,degree,worddate,salary) as   (  SELECTName,degree, Year(startworkdate) asWorddate, SALARY1+SALARY2 asSALARY from USER WHEREDegreeinch(3,4) ), TEMP2 (degree,worddate,avg_salary) as  (  SELECTdegree, Year(startworkdate) asWorddate,AVG(SALARY1+SALARY2) asavg_salary from USER WHEREDegreeinch(3,4)  GROUP  bydegree, Year(startworkdate))SELECTNAME fromTEMP1, TEMP2WHERETEMP1. Degree=TEMP2. Degree andTEMP1. Worddate=TEMP2. Worddate andSALARY<Avg_salary;

B

 withTEMP1 (Name,degree,worddate,salary) as   (  SELECTName,degree, Year(startworkdate) asWorddate, SALARY1+SALARY2 asSALARY from USER WHEREDegreeinch(3,4) ), TEMP2 (degree,worddate,avg_salary) as  (  SELECTDegree,worddate,AVG(SALARY) asavg_salary fromTEMP1GROUP  bydegree,worddate)SELECTNAME fromTEMP1, TEMP2WHERETEMP1. Degree=TEMP2. Degree andTEMP1. Worddate=TEMP2. Worddate andSALARY<Avg_salary;

Recursive usage:

Forum Home   -- Database Development   -- --db2   -- ----DB2 Article 1   -- ------DB2 Article 1 's comments 1   -- ------DB2 Article 1 's comments 2   -- ----DB2 Article 2   -- --oracle   -- Java Technology  

CREATE TABLEBBS (ParentIDINTEGER  not NULL, IDINTEGER  not NULL, NAMEVARCHAR( $) not NULL---sections, articles, reviews, etc. ); Insert  intoBBS (parentid,id,name)Values   (0,0,'Forum Home'),  (0,1,'Database Development'),  (1, One,'DB2'),  ( One,111,'DB2 Article 1'),  (111,1111,'DB2 Comments on article 1 1'),  (111,1112,'DB2 Comments on article 1 2'),  ( One, the,'DB2 Article 2'),  (1, A,'Oracle'),  (0,2,'Java Technology');

Check it out. DB2 all the articles and comments, how to do?

 with TEMP(Parentid,id,name) as  (  SELECTParentid,id,name fromBbsWHERENAME='DB2'---Statement 1UNION  All---Statement 2SELECTB.parentid,b.id,b.name fromBbs asBTEMP  asTWHEREB.parentid=T.id---Statement 3)  SELECTNAME from TEMP;---Statement 4

DB2 building a temporary result set