sql问题请教
表如下:
表名如下为kc
kc_id BOOK_ID KC_TYPE KC_NUM KC_RQBBID_O KC_RQBBID_I
1 教材1 70(转入) 1000 0 310.00(学校id)
2 教材1 71(转出) 10 310.00 0
3 教材1 71(转出) 15 310.00 0
4 教材1 71(转出) 20 310.00 311.00
5 教材1 71(转出) 20 311.00 312.00
6 教材2 70(转入) 300 0 310.00(学校id)
实现结果如下
学校 教材 转入 转出 剩余
----------------------------------------------------
310.00 教材1 1000 65 935
311.00 教材1 20 20 0
312.00 教材1 20 0 20
310.00 教材2 300 0 300
------解决方案--------------------
with xx as (
select '1' kc_id, 'jiaocai1' book_id, '70' kc_type, 1000 kc_num, '0' kc_rqbbid_o, '310.00' kc_rqbbid_i from dual union all
select '2' kc_id, 'jiaocai1' book_id, '71' kc_type, 10 kc_num, '310.00' kc_rqbbid_o, '0' kc_rqbbid_i from dual union all
select '3' kc_id, 'jiaocai1' book_id, '71' kc_type, 15 kc_num, '310.00' kc_rqbbid_o, '0' kc_rqbbid_i from dual union all
select '4' kc_id, 'jiaocai1' book_id, '71' kc_type, 20 kc_num, '310.00' kc_rqbbid_o, '311.00' kc_rqbbid_i from dual union all
select '5' kc_id, 'jiaocai1' book_id, '71' kc_type, 20 kc_num, '311.00' kc_rqbbid_o, '312.00' kc_rqbbid_i from dual union all
select '6' kc_id, 'jiaocai2' book_id, '70' kc_type, 300 kc_num, '0' kc_rqbbid_o, '310.00' kc_rqbbid_i from dual
)
select xuexiao, book_id,
nvl(sum(decode(type, 1, kc_num)), 0) zhuanru,
nvl(sum(decode(type, 2, kc_num)), 0) zhuanchu,
nvl(sum(decode(type, 1, kc_num)), 0) - nvl(sum(decode(type, 2, kc_num)), 0) shengyu
from
(
select book_id, kc_num, kc_rqbbid_i xuexiao, 1 type
from xx
where kc_rqbbid_i > 0
union all
select book_id, kc_num, kc_rqbbid_o, 2 type
from xx
where kc_rqbbid_o > 0
) group by book_id, xuexiao