Ledger table structure is as follows:
Description occurrence date specifications RuChuKu mark number
Floor tile 100 10 * 10 1 2008-01-08
Floor tile 100 10 * 10 1 2008-01-08
Floor tile 10 * 10 1 30 2008-02-06
Floor tile 60 10 * 10 2008-02-06-1
Floor tile 10 * 10 1 50 2008-04-01
Floor tile 60 10 * 10 2008-05-13-1
Floor tile 20 10 * 10 2008-02-21-1
Floor tile 50 10 * 10 2008-03-25-1
Floor tile 10 * 10 2008-05-31-1 70

Style table structure is as follows:
Name date specification initial outbound inbound final
Floor tile 2008-01 10 * 10 0 0 200 200
Floor tile of 2008-02 10 * 10 200 60 30 150
Floor tile 2008-03 10 * 10 150 50 0, 100
Floor tile of the 2008-04 10 * 10 100 0 to 50 150
Floor tile 2008-05 10 * 10 150, 130, 20

CodePudding user response:

With ta as the
(select 'tile' as names, 'the 2008-01-08 days,' 10 * 10 'guig, 1 inout, 100 vol from dual
Union all
Select 'tile', '2008-01-08', '10 * 10, 1100 from dual
Union all
Select 'tile', '2008-02-06', '10 * 10, 1 30 from dual
Union all
Select 'tile', '2008-02-06', '10 * 10' 1 'from dual
Union all
Select 'tile', '2008-04-01', '10 * 10, 1, 50 from dual
Union all
Select 'tile', '2008-05-13', '10 * 10' 1 'from dual
Union all
Select 'tile', '2008-02-21', '10 * 10, 1, 20 from dual
Union all
Select 'tile', '2008-03-25', '10 * 10, 1, 50 from dual
Union all
Select 'tile', '2008-05-31', '10 * 10' - 1 the from dual),
TB as (
Select names, substr (days 1, 7) as mon, guig, NVL (sum (decode (inout, 1, vol)), 0) as out_vol, NVL (sum (decode (inout, 1, vol)), 0) in_vol
The from ta
Group by names, substr (days 1, 7), guig
The order by names, mon)
Select names, mon, guig, out_vol in_vol, sum (in_vol - out_vol) over (order by names, guig, mon) from TB

CodePudding user response:

I provide the data only representative, if I had it a few manual calculation, if in your way, article 100000. What should I do?

CodePudding user response:

????? I write you what question article 10 w: what's the matter

CodePudding user response:

Upstairs is no problem, the previous paragraph is to build a virtual table, you can use the following that modify

With TB as (
Select names, substr (days 1, 7) as mon, guig, NVL (sum (decode (inout, 1, vol)), 0) as out_vol, NVL (sum (decode (inout, 1, vol)), 0) in_vol
The from ta
Group by names, substr (days 1, 7), guig
The order by names, mon)
Select names, mon, guig, out_vol in_vol, sum (in_vol - out_vol) over (order by names, guig, mon) from TB