sql with 使用-临时表

1、业务场景：

 有些需求需要对多个表分别统计出结果，然后使用每个表统计的结果进行汇总，这个时候临时表就发挥作用了。

2、简单使用：

with 
t1 as(select empId,yearMonth,sum(salary) as'a'
from tb_salary where yearMonth = '2017-01'
group by empId,yearMonth

),

t2 as (select empId,yearMonth,sum(salary) as'a'
from tb_salary where yearMonth = '2017-02'
group by empId,yearMonth ),

t as (select t1.empId e1, t2.empId e2, t1.empId e3, isnull(t1.yearMonth,'2017-01') m1,
 isnull(t2.yearMonth,'2017-02') m2, '与上月差额
' m3 ,isnull(t1.a,'0') a1, isnull(t2.a,'0') a2,emp.staffNo,emp.staffName,emp.orgPath
from t1 right join t2 on t1.empId = t2.empId RIGHT JOIN tb_employees emp on t2.empId = emp.empId) 

-- 行转列
select 1 n,tt.e2,tt.staffNo,tt.m1,tt.a1 from (select * from from t) tt union all
select 2 n,tt.e2,tt.staffNo,tt.m2,tt.a2 from (select * from from t) tt union all
select 3 n,tt.e2,tt.staffNo,tt.m3,(tt.a2-tt.a1) a3 from (select * from from t  where t.staffNo = '4289') tt
ORDER BY t.e2 desc,n asc;