oracle高级实用sql(with as)

with as 使用

with as使用在当需要提供多个字段数据时，避免重复性的使用union all或者使用decade函数加标签等方法，with as可以将多个字段的结果集分开查询作为一个结果集(即是看做一张新的表)，之后再对该表操作，降低sql复杂度，也降低使用union all等可能带来的失误率，with as 与join结合可一次性得到你想要的多字段数据。
简单例子如下(可执行查看)：

--with as 使用
with t_one as
 (select 'Tom' testName, '1' testId
    from dual
  union all
  select 'Jack' testName, '2' testId from dual),
t_two as
 (select '7000' testSalary, '1' testId
    from dual
  union all
  select '9000' testSalary, '2' testId from dual)
--main
select t_one.testId, t_one.testName, t_two.testSalary
  from t_one
  left join t_two
    on t_one.testId = t_two.testId;

执行结果：

the result of the example with as .png

另一技巧实例

--with as 
with tt as
 (select level as lv from dual connect by level < 20)
select lv
  from tt
 where lv > 10
   and lv < 15;

实例(不能执行，可看其写法)：

--修改with as实例总结
--
with t_store_cold as
 (select g.deptid, sum(g.netweight) netweightsum
    from wmsdba.t_datagoods g
   where g.deptid in (select t.deptid from testtable t)
     and g.inserttime = trunc(sysdate - 1)
     and g.kindname in ('冷轧')
   group by g.deptid),

--
t_store_hot as
 (select g.deptid, sum(g.netweight) netweightsum
    from wmsdba.t_datagoods g
   where g.deptid in (select t.deptid from testtable t)
     and g.inserttime = trunc(sysdate - 1)
     and g.kindname in ('热轧')
   group by g.deptid),
--
t_store_screw as
 (select g.deptid, sum(g.netweight) netweightsum
    from wmsdba.t_datagoods g
   where g.deptid in (select t.deptid from testtable t)
     and g.inserttime = trunc(sysdate - 1)
     and g.kindname in ('钢筋', '线材')
   group by g.deptid),
--
t_store_total as
 (select g.deptid, sum(g.netweight) netweightsum
    from wmsdba.t_datagoods g
   where g.deptid in (select t.deptid from testtable t)
     and g.inserttime = trunc(sysdate - 1)
   group by g.deptid)

--main
select t.deptid,
       round(nvl(co.netweightsum, 0), 2) coldWeight,
       round(nvl(h.netweightsum, 0), 2) hotWeight,
       round(nvl(co.netweightsum, 0) + nvl(h.netweightsum, 0), 2) coldhotWeight,
       round(nvl(s.netweightsum, 0), 2) screwWeight,
       round(nvl(tot.netweightsum, 0), 2) totalWeight
  from testtable t
  left join t_store_hot h
    on t.deptid = h.deptid
  left join t_store_screw s
    on t.deptid = s.deptid
  left join t_store_total tot
    on t.deptid = tot.deptid
  left join t_store_cold co
    on t.deptid = co.deptid
 where t.deptid in ('test4', 'test3', 'test2', 'test1');