with as也叫做子查询部分,首先定义一个sql片段,该sql片段会被整个sql语句所用到,为了让sql语句的可读性更高些,作为提供数据的部分,也常常用在union等集合操作中。with as最大的好处就是适当的提高代码可读性,而且如果with子句在后面要多次使用到,这可以大大的简化SQL。 关于with as子句的用法,下面两个例子看看就OK了: - -- 例子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);
复制代码
|