sql with as 用法_Oracle sql 语句中with....as的用法

Oracle的SQL语句中，With查询语句不是以select开头，而是以with关键字开头。

with...as语句增加了SQL 的可读性，构造子查询，结构会更加清晰。可以“一次分析，多次使用”，可以提高性能，达到“少读”的目标。

具体使用with....as.....的SQL语句举例：

with sql_conc_waits as

(select sql_id,

substr(sql_text, 1, 80) sql_text,

concurrency_wait_time / 1000 con_time_ms,

elapsed_time,

round(concurrency_wait_time * 100 / elapsed_time, 2) con_time_pct,

round(concurrency_wait_time * 100 / sum(concurrency_wait_time)

over(),

2) pct_of_con_time,

rank() over(order by concurrency_wait_time desc) ranking

from v$sql

where elapsed_time > 0)

select sql_text, con_time_ms, con_time_pct, pct_of_con_time

from sql_conc_waits

where ranking <= 10

order by ranking;

我们的梦想赋予生命以荣光！

所有的奇迹，只是因为不抛弃不放弃！