表
物资编号 采购量 库存量 仓库
seq item_id buy_qt stock_qt store
1 1 100 11 ST1
2 1 100 10 ST1
3 1 110 50 ST2
4 2 100 11 ST1
5 2 100 11 ST2
6 3 100 11 ST1
7 3 100 11 ST2怎么获取结果:(即求各物资在各个仓库的总采购量和库存量)
seq item_id buy_qt stock_qt
st1 st2 st1 st2
1 1 200 110 21 50
2 2 100 100 11 11
3 3 100 100 11 11
物资编号 采购量 库存量 仓库
seq item_id buy_qt stock_qt store
1 1 100 11 ST1
2 1 100 10 ST1
3 1 110 50 ST2
4 2 100 11 ST1
5 2 100 11 ST2
6 3 100 11 ST1
7 3 100 11 ST2怎么获取结果:(即求各物资在各个仓库的总采购量和库存量)
seq item_id buy_qt stock_qt
st1 st2 st1 st2
1 1 200 110 21 50
2 2 100 100 11 11
3 3 100 100 11 11
sum(buy_qt_st1) buy_qt_st1,
sum(buy_qt_st2) buy_qt_st2,
sum(stock_qt_ST1) stock_qt_ST1,
sum(stock_qt_ST2) stock_qt_ST2
from(
select item_id,
decode(STORE,'ST1',BUY_QT,0) buy_qt_ST1,
decode(STORE,'ST2',BUY_QT,0) buy_qt_ST2,
decode(STORE,'ST1',STOCK_QT,0) stock_qt_ST1,
decode(STORE,'ST2',STOCK_QT,0) stock_qt_ST2,
store
from ttt)
group by item_id
order by item_id
select rownum as seq, t.*
from (select item_id,
sum(case when lower(store) = 'st1' then buy_qt else 0 end) as st1_buy,
sum(case when lower(store) = 'st2' then buy_qt else 0 end) as st2_buy,
sum(case when lower(store) = 'st1' then stock_qt else 0 end) as st1_stock,
sum(case when lower(store) = 'st2' then stock_qt else 0 end) as st2_sotck
from test
group by item_id) t;
(
select 1 a, 100 b, 11 c, 'ST1' d from dual
union all
select 1, 100, 10, 'ST1' from dual
union all
select 1, 110, 50, 'ST2' from dual
union all
select 2, 100, 11, 'ST1' from dual
union all
select 2, 100, 11, 'ST2' from dual
union all
select 3, 100, 11, 'ST1' from dual
union all
select 3, 100, 11, 'ST2' from dual
)
select a,sum(b),sum(c),sum(d),sum(e)
from
(
select distinct a,
sum(case when d='ST1' then b else 0 end) b,
sum(case when d='ST2' then b else 0 end) c,
sum(case when d='ST1' then c else 0 end) d,
sum(case when d='ST2' then c else 0 end) e
from temp
group by a,d
)group by a----result:
1 200 110 21 50
2 100 100 11 11
3 100 100 11 11
SUM(DECODE(STORE,'ST1',BUY_QT)) BUY_QTST1,
SUM(DECODE(STORE,'ST2',BUY_QT))BUY_QTST2,
SUM(DECODE(STORE,'ST1',STOCK_QT))STOCK_QTST1,
SUM(DECODE(STORE,'ST2',STOCK_QT))STOCK_QTST2
FROM OLAD
GROUP BY ITEM_ID
(
select 1 item_id, 100 buy_qt, 11 stock_qt, 'ST1' store from dual
union all
select 1, 100, 10, 'ST1' from dual
union all
select 1, 110, 50, 'ST2' from dual
union all
select 2, 100, 11, 'ST1' from dual
union all
select 2, 100, 11, 'ST2' from dual
union all
select 3, 100, 11, 'ST1' from dual
union all
select 3, 100, 11, 'ST2' from dual
)
select row_number()over(partition by 1 order by item_id) seq,item_id,
sum(case when store='ST1' then buy_qt else 0 end) buy_qt_st1,
sum(case when store='ST2' then buy_qt else 0 end) buy_qt_st2,
sum(case when store='ST1' then stock_qt else 0 end) stock_qt_st1,
sum(case when store='ST2' then stock_qt else 0 end) stock_qt_st2
from temp
group by item_id