DB2 With As 临时表的使用
一、With As 的使用场景
1.子查询过多时,且同一个子查询需要被多次使用的时候;
2.Union All 时,每个被Union All的查询,有部分查询条件重复时,可以用With As查询中间表,避免重复查询的缺点;
缺点:With As的查询结果集相对于中间表不支持索引,而且With As查询的结果数据量大时,内存开销就大。
二、代码实现
1.
WITH temp_table_name1 AS (SELECT * FROM [schema].[table_name]),
temp_table_name2 AS (SELECT * FROM [schema].[table_name]),
……… // “,”“;”
2.
SELECT * FROM temp_table_name1 ;
SELECT * FROM temp_table_name2 ;
3.
WITH AAA AS (SELECT * FROM LIMS.MDM_D_AGT_GHOST),
BBB AS (SELECT * FROM LIMS.MDM_D_AGT_GHOST),
CCC AS (SELECT * FROM LIMS.MDM_D_AGT_GHOST)
SELECT * FROM AAA;
SELECT * FROM BBB;
SELECT * FROM CCC;