oracle中with as子句的用法小结 - 度量快速开发平台-专业、快速的软件定制快开平台

with as也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。with as最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL。

-- 例子1
WITH dept_costs AS
(SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg FROM avg_cost)
ORDER BY department_name;
-- 例子2
WITH sql1 AS
(SELECT to_char(a) s_name FROM test_tempa),
sql2 AS
(SELECT to_char(b) s_name
FROM test_tempb
WHERE NOT EXISTS (SELECT s_name FROM sql1 WHERE rownum = 1))
SELECT *
FROM sql1
UNION ALL
SELECT *
FROM sql2
UNION ALL
SELECT 'no records'
FROM dual
WHERE NOT EXISTS (SELECT s_name FROM sql1 WHERE rownum = 1) AND NOT EXISTS (SELECT s_name
FROM sql2
WHERE rownum = 1);

复制代码