select a.name, to_char(b.accept_date,'yyyymm'), count(b.id) from 部門 a, 業務表 b where a.id=b.depts_id(+) and ( to_char(b.accept_date,'yyyymm')=to_char(sysdate,'yyyymm') or to_char(b.accept_date,'yyyymm')=to_char(add_months(sysdate,-1) or to_char(b.accept_date,'yyyymm')=to_char(add_months(sysdate,-12),'yyyymm') ) group by a.name,to_char(b.accept_date,'yyyymm')
select a.id, a.Name, nvl(sum(decode(to_char(接单时间, 'yyyymm') = to_char(sysdate, 'yyyymm'), 1, 0)), 0) as 本月接单笔数, nvl(sum(decode(to_char(接单时间, 'yyyymm') = to_char(add_months(sysdate, -1), 'yyyymm'), 1, 0)), 0) as 上月接单笔数, nvl(sum(decode(to_char(接单时间, 'yyyymm') = to_char(add_months(sysdate, -12), 'yyyymm'), 1, 0)), 0) as 去年当月接单笔数, from 部门 a left join 业务单表 b on a.id = b.depts_id
to_char(b.accept_date,'yyyymm'),
count(b.id)
from 部門 a,
業務表 b
where a.id=b.depts_id(+)
and ( to_char(b.accept_date,'yyyymm')=to_char(sysdate,'yyyymm')
or to_char(b.accept_date,'yyyymm')=to_char(add_months(sysdate,-1)
or to_char(b.accept_date,'yyyymm')=to_char(add_months(sysdate,-12),'yyyymm')
)
group by a.name,to_char(b.accept_date,'yyyymm')
a.Name,
nvl(sum(decode(to_char(接单时间, 'yyyymm') = to_char(sysdate, 'yyyymm'),
1,
0)),
0) as 本月接单笔数,
nvl(sum(decode(to_char(接单时间, 'yyyymm') =
to_char(add_months(sysdate, -1), 'yyyymm'),
1,
0)),
0) as 上月接单笔数,
nvl(sum(decode(to_char(接单时间, 'yyyymm') =
to_char(add_months(sysdate, -12), 'yyyymm'),
1,
0)),
0) as 去年当月接单笔数,
from 部门 a
left join 业务单表 b on a.id = b.depts_id
a.Name