子查询with as

子查询with as 使用
1.查询多个数据表时，最简单的方法就是使用with as 子查询，以下示例查询两张数据表：
1)需求：统计各个分行的贷款金额(需要合计两张中的数据)
2)数据表a;
3)数据表b;
with a as
(
   select a.branchCode,c.orgName as branchName,nvl(sum(a.data),0) as data
   from a a
   left join c c on a.branchCode = c.orgCode
   group by a.branchCode,c.orgName
),
b as
(
   select b.branchCode,c.orgName as branchName,nvl(sum(b.data),0) as data
   from b b
   left join c c on b.branchCode = c.orgCode
   group by b.branchCode,c.orgName
)
select nvl(a.branchCode,b.branchCode) as branchCode,
       nvl(a.branchName,b.branchName) as branchName,
       nvl(a.data,0)+nvl(b.data,0) as data
from a
full join b on a.branchCode = b.branchCode
怎么样？with as 就是如些的简单，举一反三，只要你掌握了这个函数，理解了它真正的含义，不管有几张表，逻辑如何复杂，都会让你得心应手……
真是过瘾呀，写sql真是一种享受…… 以后会陆续写sql^^^^呵呵，写sql我的最爱………………
路过的人，不管你对sql有什么独特的见解，还是对sql有多深厚的功底，都不要对我写的sql嗤之以鼻……
我写的sql我骄傲……