引言:
WITH子句或子查询分解子句是SQL-99标准的一部分,在Oracle 9.2中被添加到Oracle SQL语法中。WITH子句可以作为内联视图处理,也可以作为临时表解析。后者的优点是,对子查询的重复引用可能更有效,因为可以轻松地从临时表检索数据,而不是由每个引用请求数据。实际生产环境当然也不能乱用,我们需要结合执行计划,10046 autotrace等工具,评估with语句的执行效率。
那么,在Oracle PL/SQL中,WITH子句是一个子查询分解子句,用于创建命名的子查询块这个块充当SQL语句的虚拟表或内联视图。它最初是在Oracle 9.2中引入的。它减少了查询中一个表别名的多个引用的开销。with子句子查询块的作用域是与之关联的SELECT查询。
总结一下,就是一句话:WITH query_name子句允许将名称分配给子查询块。然后,通过指定查询名称,可以在查询中的多个位置引用子查询块。Oracle通过将查询名作为内联视图或临时表来优化查询。
语法:
单个别名:
WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM ;
多个别名:
WITH <alias_one> AS
(subquery_sql_statement),
<alias_two> AS
(sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
WITH和PIVOT(列转行)合用例子:
WITH TEMP AS
(SELECT TYPE, NVL(COUNT(1), 0) COU
FROM (SELECT A.CONTENT AS TYPE
FROM TBTASKPATROLINFOEQUIP T, TBIZCATTACH A
WHERE T.ID = A.BIZC_ID
AND A.CONTENT IN ('jb_wd', 'jb_dy', 'jb_dl')
AND A.PIC_SOURCE = '0'
AND A.BIZC_TYPE IN ('jb_xltjd',
'jb_dlbxjk',
'jb_xj',
'jb_dlzdt',
'jb_zskglc',
'jb_zspbgdy',
'jb_10qt')
AND T.PATROLTASKID =''
GROUP BY A.CONTENT, A.BIZC_ID)
GROUP BY TYPE),
TEMP2 AS
(SELECT 'jb_wd' AS TYPE1, 'jb_dy' AS TYPE2, 'jb_dl' AS TYPE3 FROM DUAL)
SELECT *
FROM (SELECT A.TYPE, NVL(B.COU, 0) NUM
FROM (SELECT TYPE
FROM TEMP2 UNPIVOT(TYPE FOR COL IN(TYPE1, TYPE2, TYPE3))) A,
TEMP B
WHERE A.TYPE = B.TYPE(+))
PIVOT (SUM(NUM) FOR TYPE IN('jb_wd' AS CWDS,
'jb_dy' AS CDYS,
'jb_dl' AS CDLS))