with t_dec as(
select to_date('2009-12-1','yyyy-mm-dd') bizDate, 'A' number_, '工序A' node, 30 qty from dual union all
select to_date('2009-12-1','yyyy-mm-dd') bizDate, 'B' number_, '工序C' node, 40 qty from dual union all
select to_date('2009-12-2','yyyy-mm-dd') bizDate, 'A' number_, '工序B' node, 30 qty from dual union all
select to_date('2009-12-3','yyyy-mm-dd') bizDate, 'A' number_, '工序C' node, 40 qty from dual union all
select to_date('2009-12-4','yyyy-mm-dd') bizDate, 'B' number_, '工序D' node, 30 qty from dual union all
select to_date('2009-12-5','yyyy-mm-dd') bizDate, 'A' number_, '工序A' node, 10 qty from dual)
select number_,
sum(decode(node,'工序A',qty)) 工序A,
sum(decode(node,'工序B',qty)) 工序B,
sum(decode(node,'工序C',qty)) 工序C,
sum(decode(node,'工序D',qty)) 工序D
from t_dec
where bizDate between to_date('2009-12-1','yyyy-mm-dd') and to_date('2009-12-5','yyyy-mm-dd')
group by number_
union all
select '合计' bizDate, sum(工序A), sum(工序B), sum(工序C), sum(工序D)
from(select number_,
sum(decode(node,'工序A',qty)) 工序A,
sum(decode(node,'工序B',qty)) 工序B,
sum(decode(node,'工序C',qty)) 工序C,
sum(decode(node,'工序D',qty)) 工序D
from t_dec
where bizDate between to_date('2009-12-1','yyyy-mm-dd') and to_date('2009-12-5','yyyy-mm-dd')
group by number_);