with 子句

WITH子句提供了一种定义临时表的操作方法
使用WITH子句将emp表中的数据定义为临时表
WITH e AS (SELECT * FROM emp) 创建临时表e

创建多个with子句，可以用逗号隔开
with q as(…），w as(…)

查询每个部门中各个职位的总工资 —— 按照部门编号及职位进行分组
select deptno,job,sum(sal) from emp group by deptno,job
select deptno,
sum (decode (job,‘PRESIDENT’,sal,0)) PRESIDENT_job,
sum (decode (job,‘CLERK’,sal,0)) CLERK_job,
sum (decode (job,‘MANAGER’,sal,0)) MANAGER_job,
sum (decode (job,‘ANALYST’,sal,0)) ANALYST_job,
sum (decode (job,‘SALESMAN’,sal,0)) SALESMAN_job
from emp
group by deptno;