with as 使用

with as使用在當(dāng)需要提供多個(gè)字段數(shù)據(jù)時(shí)，避免重復(fù)性的使用union all或者使用decade函數(shù)加標(biāo)簽等方法，with as可以將多個(gè)字段的結(jié)果集分開(kāi)查詢作為一個(gè)結(jié)果集(即是看做一張新的表)，之后再對(duì)該表操作，降低sql復(fù)雜度，也降低使用union all等可能帶來(lái)的失誤率，with as 與join結(jié)合可一次性得到你想要的多字段數(shù)據(jù)。
簡(jiǎn)單例子如下(可執(zhí)行查看)：

--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;

執(zhí)行結(jié)果：

the result of the example with as .png

另一技巧實(shí)例

--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;

實(shí)例(不能執(zhí)行，可看其寫(xiě)法)：

--修改with as實(shí)例總結(jié)
--
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');