- table1
- 月份mon 部门dep 业绩yj
- -------------------------------
- 一月份 01 10
- 一月份 02 10
- 一月份 03 5
- 二月份 02 8
- 二月份 04 9
- 三月份 03 8
- table2
- 部门dep 部门名称dname
- --------------------------------
- 01 国内业务一部
- 02 国内业务二部
- 03 国内业务三部
- 04 国际业务部
- table3 (result)
- 部门dep 一月份 二月份 三月份
- --------------------------------------
- 01 10 null null
- 02 10 8 null
- 03 null 5 8
- 04 null null 9
- ------------------------------------------
- 1)
- select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
- from table1 a,table2 b,table2 c,table2 d
- where a.部门dep = b.部门dep and b.月份mon = '一月份' and
- a.部门dep = c.部门dep and c.月份mon = '二月份' and
- a.部门dep = d.部门dep and d.月份mon = '三月份' and
- 2)
- select a.dep,
- sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
- sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
- sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
- sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
- sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
- sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
- sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
- sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
- sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
- sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
- sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
- sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
- from table2 a left join table1 b on a.dep=b.dep