mysql sql with as_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');