Oracle with AS Usage

The WITH query statement is preceded with a, which is equivalent to building a temporary table before the query, the specified query results are stored with the temporary table, and can then be used more than once for further analysis and processing.

Grammar:

With _temptable as (SELECT * from table)

SELECT * FROM _temptable

Example:

With _tempstudent as (

SELECT * FROM T_student t where class = ' First Class 23 '

)

Select Sex,count (1) nums from _tempstudent where sex = ' man ' and Height > ' 170 '

UNION ALL

Select Sex,count (1) nums from _tempstudent where sex = ' female ' and height > ' 160 '

Multiple with as usage each temporary stock is directly separated by ","

with T1 as (

SELECT * from student where name in (' Zhang San ', ' John Doe ')

), T2 as (

SELECT * from student where name in (' Harry ')

)

SELECT * FROM t1

Union

SELECT * FROM T2

If the with as has nested conditions, multiple with AS, the back of the as interior can invoke the declared temporary object directly

with T1 as (

SELECT * from student where name in (' Zhang San ', ' John Doe ')

), T2 as (

SELECT * from t1 where name in (' Harry ')

)

SELECT * FROM T2

Oracle with AS Usage