表如下:
表名如下为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
表名如下为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
表如下:
表名如下为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
KC_RQBBID_I代表转入校区 ,为0时代表没有转入校区
然后sum(KC_NUM )
是写错了,不好意思
HTML code
表如下:
表名如下为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 45 955
311.00 教材1 20 20 0
312.00 教材1 20 0 20
310.00 教材2 300 0 300
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
----------------------------------------------------
310.00 jiaochai1 1000 45 955
311.00 jiaochai1 20 20 0
312.00 jiaochai1 20 0 20
310.00 jiaochai2 300 0 300
里面不一样的稍微修改一下就可以了。
(
SELECT 1 ID, ' a1' AS BOOK, '70' DIRECT, 1000 NUM, 0 OUT_S, 310.00 INPUT_S
FROM DUAL
UNION ALL
SELECT 2 ID, ' a1' AS BOOK, '71' DIRECT, 10 NUM, 310.00 OUT_S, 0 INPUT_S
FROM DUAL
UNION ALL
SELECT 3 ID, ' a1' AS BOOK, '71' DIRECT, 15 NUM, 310.00 OUT_S, 0 INPUT_S
FROM DUAL
UNION ALL
SELECT 4 ID,
' a1' AS BOOK,
'71' DIRECT,
20 NUM,
310.00 OUT_S,
311.00 INPUT_S
FROM DUAL
UNION ALL
SELECT 5 ID,
' a1' AS BOOK,
'71' DIRECT,
20 NUM,
311.00 OUT_S,
312.00 INPUT_S
FROM DUAL
UNION ALL
SELECT 6 ID, ' a2' AS BOOK, '70' DIRECT, 300 NUM, 0 OUT_S, 310.00 INPUT_S
FROM DUAL),
A AS
(SELECT BOOK,
DECODE(OUT_S, 0, INPUT_S, OUT_S) OUT_S,
DECODE(INPUT_S, 0, OUT_S, INPUT_S) INPUT_S,
DIRECT,
SUM(NUM) T
FROM T
GROUP BY BOOK,
DECODE(OUT_S, 0, INPUT_S, OUT_S),
DECODE(INPUT_S, 0, OUT_S, INPUT_S),
DIRECT)--SELECT * FROM a,
B AS
(SELECT OUT_S, BOOK, DIRECT, T
FROM A
UNION ALL
SELECT INPUT_S, BOOK, DECODE(DIRECT, '71', '70', '71') D, T
FROM A
WHERE OUT_S <> INPUT_S)SELECT OUT_S, BOOK, SUM(CASE WHEN DIRECT = '70' THEN T ELSE 0 END), SUM(CASE WHEN DIRECT = '71' THEN T ELSE 0 END), SUM(CASE WHEN DIRECT = '70' THEN T ELSE 0 END) - SUM(CASE WHEN DIRECT = '71' THEN T ELSE 0 END)
FROM B
GROUP BY OUT_S, BOOKORDER BY 1, 2, 3