如何根据流水帐记录使用一条SQL产生某月财务期初、期未库存表?
流水帐表结构如下：
品名 发生日期 规格 入出库标志 数量
马赛克 2008-01-08 10*10 1 100
马赛克 2008-02-06 10*10 1 30
马赛克 2008-04-01 10*15 1 50
马赛克 2008-05-13 10*14 1 60
马赛克 2008-01-16 10*14 -1 30
马赛克 2008-02-21 10*15 -1 20
马赛克 2008-03-25 10*10 -1 50
马赛克 2008-05-31 10*10 -1 70
马赛克 2008-06-09 10*14 -1 10
大理石板 2008-01-08 100*100 1 100
大理石板 2008-02-06 100*100 1 30
大理石板 2008-04-01 100*150 1 50
大理石板 2008-05-13 100*140 1 60
大理石板 2008-01-16 100*140 -1 30
大理石板 2008-02-21 100*150 -1 20
大理石板 2008-03-25 100*100 -1 50
大理石板 2008-05-31 100*100 -1 70
大理石板 2008-06-09 100*140 -1 10
统计结果如下：
SQL输入5月，含五月的数据
月份 品名 规格 期初数量 本期收入 本期发出 期未数量
2008-05 马赛克 10*10 80 0 70 10
2008-05 马赛克 10*14 -30 60 0 30
2008-05 马赛克 10*15 30 0 0 30
2008-05 大理石板 100*100 80 0 70 10
2008-05 大理石板 100*140 -30 60 0 30
2008-05 大理石板 100*150 30 0 0 30
此SQL涉及几个问题：
1.将入出库标志变横显示.
2.期未数量运算显示
3.期初数量运算显示
流水帐表结构如下：
品名 发生日期 规格 入出库标志 数量
马赛克 2008-01-08 10*10 1 100
马赛克 2008-02-06 10*10 1 30
马赛克 2008-04-01 10*15 1 50
马赛克 2008-05-13 10*14 1 60
马赛克 2008-01-16 10*14 -1 30
马赛克 2008-02-21 10*15 -1 20
马赛克 2008-03-25 10*10 -1 50
马赛克 2008-05-31 10*10 -1 70
马赛克 2008-06-09 10*14 -1 10
大理石板 2008-01-08 100*100 1 100
大理石板 2008-02-06 100*100 1 30
大理石板 2008-04-01 100*150 1 50
大理石板 2008-05-13 100*140 1 60
大理石板 2008-01-16 100*140 -1 30
大理石板 2008-02-21 100*150 -1 20
大理石板 2008-03-25 100*100 -1 50
大理石板 2008-05-31 100*100 -1 70
大理石板 2008-06-09 100*140 -1 10
统计结果如下：
SQL输入5月，含五月的数据
月份 品名 规格 期初数量 本期收入 本期发出 期未数量
2008-05 马赛克 10*10 80 0 70 10
2008-05 马赛克 10*14 -30 60 0 30
2008-05 马赛克 10*15 30 0 0 30
2008-05 大理石板 100*100 80 0 70 10
2008-05 大理石板 100*140 -30 60 0 30
2008-05 大理石板 100*150 30 0 0 30
此SQL涉及几个问题：
1.将入出库标志变横显示.
2.期未数量运算显示
3.期初数量运算显示
已经发生的第一笔数据不可改变,用一个JOB运行,每月的第一天把上月数据结算一次;如果你用一条sql语句查询,那期初库存是多少?还不是由另外一条sql语句出来计算出上月的期末库存
那上月的上月期末库存又是多少?
你总不能写那么的套嵌sql语句
select aa.品名,
aa.month,
aa.规格,
bb.月初,
case
when aa.月末 - bb.月初 >= 0 then
aa.月末 - bb.月初
else
0
end 本期收入,
case
when aa.月末 - bb.月初 <= 0 then
bb.月初 - aa.月末
else
0
end 本期发出,
aa.月末
from (select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月末
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期 desc) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) aa,
(select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月初
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) bb where aa.品名 = bb.品名
and aa.month = bb.month
and aa.规格 = bb.规格
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 就可可得到日结，月结和报表就可以写了！不过好像有点复杂
望能有其它办法加快我的查询速度。
以下是上面提到通过日结表解决的一条语句完成的SQLselect tb.pm,tb.frq,tb.gg,
decode(row_number() over(partition by tb.pm order by tb.PM,tb.RQ,tb.gg),1,0,
LAG(l_bqlj, 1, 0) OVER (ORDER BY tb.PM,TB.RQ,tb.gg)) AS prev_BQJC,
tb.bqrk,tb.bqck,tb.l_bqlj
from
(select ta.pm,ta.gg,ta.rq,ta.bqrk,ta.bqck,
SUM(ta.bqlj) OVER (PARTITION BY ta.pm,ta.gg
ORDER BY ta.PM,ta.gg,ta.rq
RANGE UNBOUNDED PRECEDING) l_bqlj
from
(SELECT LSZ.品名 pm, LZS.规格 GG,to_char(LSZ.发生日期,'yyyy-mm') rq,
sum(decode(LSZ.入出库标志,1, LSZ.数量,0)) bqrk,
sum(decode(LSZ.入出库标志,-1,LSZ.数量,0)) bqck,
sum(decode(LSZ.入出库标志,1, LSZ.数量,0)) -
sum(decode(LSZ.入出库标志,-1,LSZ.数量,0)) bqlj
FROM (select 品名,发生日期,规格,入出库标志,数量
from 流水帐 b
where to_char(发生日期,'yyyymm') = '200810'
union
SELECT 品名,发生日期,规格,入出库标志,数量
FROM 月结表 a
WHERE to_char(发生日期,'yyyymm') = ADD_MONTHS(TO_DATE('200810','YYYYMM'),-1)) LSZ
group by to_char(LSZ.发生日期,'yyyy-mm'),LSZ.品名
order by to_char(LSZ.发生日期,'yyyy-mm'),LSZ.品名) TA) tB
下南是不需要月结表时统计当月的期初、期未库存数据。select tb.pm,tb.frq,tb.gg,
decode(row_number() over(partition by tb.pm order by tb.PM,tb.RQ,tb.gg),1,0,
LAG(l_bqlj, 1, 0) OVER (ORDER BY tb.PM,TB.RQ,tb.gg)) AS prev_BQJC,
tb.bqrk,tb.bqck,tb.l_bqlj
from
(select ta.pm,ta.gg,ta.rq,ta.bqrk,ta.bqck,
SUM(ta.bqlj) OVER (PARTITION BY ta.pm,ta.gg
ORDER BY ta.PM,ta.gg,ta.rq
RANGE UNBOUNDED PRECEDING) l_bqlj
from
(SELECT LSZ.品名 pm, LZS.规格 GG,to_char(LSZ.发生日期,'yyyy-mm') rq,
sum(decode(LSZ.入出库标志,1, LSZ.数量,0)) bqrk,
sum(decode(LSZ.入出库标志,-1,LSZ.数量,0)) bqck,
sum(decode(LSZ.入出库标志,1, LSZ.数量,0)) -
sum(decode(LSZ.入出库标志,-1,LSZ.数量,0)) bqlj
FROM (select 品名,发生日期,规格,入出库标志,数量
from 流水帐 b
where to_char(发生日期,'yyyymm') >= '200810'
union
SELECT a.品名,to_date('200810','yyyymm') 发生日期,a.规格,0 入出库标志,0 数量
FROM 流水帐 a
GROUP BY a.品名,a.发生日期,a.规格
group by to_char(LSZ.发生日期,'yyyy-mm'),LSZ.品名
order by to_char(LSZ.发生日期,'yyyy-mm'),LSZ.品名) TA) tB
两段语句间的差别就在于虚拟表A的使用。
第一段的虚拟表A不用解释，他就是把虚拟表变成实体的月结表。
第二段是中原始数据的来源因为是整个流水帐表，所以虚拟表B中不需要添加限制等于要求月的条件，而
只要在使A中添国一份当月的空数据，即可将以前月份的结转到本月。
select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 日结
from tablea t这个应该不难！
在日结表中按月统计一个月中最小时间的一条日结为月初值，最大的为月末值！
CREATE TABLE test_finance(品名 VARCHAR2(20),发生日期 DATE,规格 VARCHAR2(20),入出库标志 NUMBER,数量 NUMBER);SQL> SELECT TO_CHAR(发生日期, 'YYYY-MM') 月份, 品名, 规格, 期初数量, 本期收入, 本期发出, 期未数量
2 FROM (SELECT TF.*,
3 SUM(入出库标志 * 数量) OVER(PARTITION BY 品名, 规格 ORDER BY 发生日期) - (入出库标志 * 数量) 期初数量,
4 DECODE(SIGN(入出库标志), 1, 数量, 0) 本期收入,
5 DECODE(SIGN(入出库标志), -1, 数量, 0) 本期发出,
6 SUM(入出库标志 * 数量) OVER(PARTITION BY 品名, 规格 ORDER BY 发生日期) 期未数量
7 FROM TEST_FINANCE TF) TT
8 WHERE TO_CHAR(发生日期, 'YYYY-MM') = '2008-05';月份 品名 规格 期初数量 本期收入 本期发出 期未数量
------- -------------------- -------------------- ---------- ---------- ---------- ----------
2008-05 大理石板 100*100 80 0 70 10
2008-05 大理石板 100*140 -30 60 0 30
2008-05 马赛克 10*10 80 0 70 10
2008-05 马赛克 10*14 -30 60 0 30
"第二段是中原始数据的来源因为是整个流水帐表，所以虚拟表B中不需要添加限制等于要求月的条件，而
只要在使A中添国一份当月的空数据，即可将以前月份的结转到本月。 "
aa.month,
aa.规格,
bb.月初,
case
when aa.月末 - bb.月初 >= 0 then
aa.月末 - bb.月初
else
0
end 本期收入,
case
when aa.月末 - bb.月初 <= 0 then
bb.月初 - aa.月末
else
0
end 本期发出,
aa.月末
from (select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月末
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期 desc) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) aa,
(select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月初
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) - to_number(t.入出库标志) * t.数量 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) bb where aa.品名 = bb.品名
and aa.month = bb.month
and aa.规格 = bb.规格;
--还是有点没写好，参考一下吧：SQL> select * from io_tab;P_NAME IO_DATE P_TYPE FLAG P_COUNT
---------------- ----------- -------------------- ---- ---------
马赛克 2008-1-8 10*10 1 100
马赛克 2008-2-6 10*10 1 30
马赛克 2008-4-1 10*15 1 50
马赛克 2008-5-13 10*14 1 60
马赛克 2008-3-25 10*10 -1 50
马赛克 2008-5-31 10*10 -1 70
马赛克 2008-6-9 10*14 -1 10
大理石板 2008-1-8 100*100 1 100
大理石板 2008-2-6 100*100 1 30
大理石板 2008-4-1 100*150 1 50
大理石板 2008-5-13 100*140 1 60
大理石板 2008-1-16 100*140 -1 30
大理石板 2008-2-21 100*150 -1 20
大理石板 2008-3-25 100*100 -1 50
大理石板 2008-5-31 100*100 -1 70
大理石板 2008-6-9 100*140 -1 1016 rows selectedSQL>
SQL> select t.a 月份,
2 t.b 品名,
3 t.c 规格,
4 t1.d 期初数量,
5 t2.d 本期收入,
6 t3.d 本期发出,
7 t4.d 期未数量
8 from (select to_char(io_date, 'yyyy-mm') a, p_name b, p_type c
9 from io_tab
10 group by to_char(io_date, 'yyyy-mm'), p_name, p_type) t,
11 (select to_char(add_months(io_date, -1), 'yyyy-mm') a,
12 p_name b,
13 p_type c,
14 sum(to_number(flag) * p_count) d
15 from io_tab
16 where to_char(add_months(io_date, -1), 'yyyy-mm') >= '2008-01'
17 and to_char(add_months(io_date, -1), 'yyyy-mm')<=to_char(add_months(io_date, -1), 'yyyy-mm')
18 group by to_char(add_months(io_date, -1), 'yyyy-mm'),
19 p_name,
20 p_type) t1,
21 (select to_char(io_date, 'yyyy-mm') a,
22 p_name b,
23 p_type c,
24 sum(decode(flag, 1, p_count, 0)) d
25 from io_tab
26 group by to_char(io_date, 'yyyy-mm'), p_name, p_type) t2,
27 (select to_char(io_date, 'yyyy-mm') a,
28 p_name b,
29 p_type c,
30 sum(decode(flag, -1, p_count, 0)) d
31 from io_tab
32 group by to_char(io_date, 'yyyy-mm'), p_name, p_type) t3,
33 (select to_char(io_date, 'yyyy-mm') a,
34 p_name b,
35 p_type c,
36 sum(to_number(flag) * (p_count)) d
37 from io_tab
38 where to_char(io_date, 'yyyy-mm') >= '2008-01'
39 and to_char(io_date, 'yyyy-mm') <= to_char(io_date, 'yyyy-mm')
40 group by to_char(io_date, 'yyyy-mm'), p_name, p_type) t4
41 where t.a = to_char(add_months(to_date(t1.a, 'yyyy-mm'), 1), 'yyyy-mm')
42 and t.a = t2.a(+)
43 and t.a = t3.a(+)
44 and t.a = t4.a(+)
45 and t.b = t1.b(+)
46 and t.b = t2.b(+)
47 and t.b = t3.b(+)
48 and t.b = t4.b(+)
49 and t.c = t1.c(+)
50 and t.c = t2.c(+)
51 and t.c = t3.c(+)
52 and t.c = t4.c(+)
53 order by t.a, t.b, t.c;月份 品名 规格 期初数量 本期收入 本期发出 期未数量
------- ---------------- -------------------- ---------- ---------- ---------- ----------
2008-02 大理石板 100*100 30 30 0 30
2008-02 大理石板 100*150 -20 0 20 -20
2008-02 马赛克 10*10 30 30 0 30
2008-03 大理石板 100*100 -50 0 50 -50
2008-03 马赛克 10*10 -50 0 50 -50
2008-04 大理石板 100*150 50 50 0 50
2008-04 马赛克 10*15 50 50 0 50
2008-05 大理石板 100*100 -70 0 70 -70
2008-05 大理石板 100*140 60 60 0 60
2008-05 马赛克 10*10 -70 0 70 -70
2008-05 马赛克 10*14 60 60 0 60
2008-06 大理石板 100*140 -10 0 10 -10
2008-06 马赛克 10*14 -10 0 10 -1013 rows selectedSQL>
create table TABLEA
(
品名 VARCHAR2(30),
发生日期 DATE,
规格 VARCHAR2(20),
入出库标志 VARCHAR2(10),
数量 NUMBER
);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('06-02-2008', 'dd-mm-yyyy'), '10*10', '1', 30);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('01-04-2008', 'dd-mm-yyyy'), '10*15', '1', 50);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('13-05-2008', 'dd-mm-yyyy'), '10*14', '1', 60);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('16-01-2008', 'dd-mm-yyyy'), '10*14', '-1', 30);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('21-02-2008', 'dd-mm-yyyy'), '10*15', '-1', 20);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('25-03-2008', 'dd-mm-yyyy'), '10*10', '-1', 50);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('31-05-2008', 'dd-mm-yyyy'), '10*10', '-1', 70);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('09-06-2008', 'dd-mm-yyyy'), '10*14', '-1', 10);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('08-01-2008', 'dd-mm-yyyy'), '100*100', '1', 100);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('06-02-2008', 'dd-mm-yyyy'), '100*100', '1', 30);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('01-04-2008', 'dd-mm-yyyy'), '100*150', '1', 50);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('13-05-2008', 'dd-mm-yyyy'), '100*140', '1', 60);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('16-01-2008', 'dd-mm-yyyy'), '100*140', '-1', 30);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('21-02-2008', 'dd-mm-yyyy'), '100*150', '-1', 20);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('25-03-2008', 'dd-mm-yyyy'), '100*100', '-1', 50);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('31-05-2008', 'dd-mm-yyyy'), '100*100', '-1', 70);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('09-06-2008', 'dd-mm-yyyy'), '100*140', '-1', 10);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('12-01-2008', 'dd-mm-yyyy'), '10*10', '1', 20);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('大理石板', to_date('20-02-2008', 'dd-mm-yyyy'), '100*100', '1', 45);
insert into TABLEA (品名, 发生日期, 规格, 入出库标志, 数量)
values ('马赛克', to_date('08-01-2008', 'dd-mm-yyyy'), '10*10', '1', 100);
commit;
select aa.品名,
aa.month,
aa.规格,
bb.月初,
case
when aa.月末 - bb.月初 >= 0 then
aa.月末 - bb.月初
else
0
end 本期收入,
case
when aa.月末 - bb.月初 <= 0 then
bb.月初 - aa.月末
else
0
end 本期发出,
aa.月末
from (select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月末
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期 desc) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) aa,
(select a.品名,
to_char(a.发生日期, 'yyyy-mm') month,
a.规格,
a.日结 月初
from (select t.品名,
t.发生日期,
t.规格,
sum(to_number(t.入出库标志) * t.数量) over(partition by t.品名, t.规格 order by t.品名, t.发生日期) - to_number(t.入出库标志) * t.数量 日结
from tablea t) a,
(select 品名, 发生日期, 规格
from (select t.*,
row_number() over(partition by t.品名, t.规格, to_char(t.发生日期, 'yyyy-mm') order by t.品名, t.规格, t.发生日期) rn
from tablea t)
where rn = 1) b
where a.品名 = b.品名
and a.发生日期 = b.发生日期
and a.规格 = b.规格) bb
where aa.品名 = bb.品名
and aa.month = bb.month
and aa.规格 = bb.规格;
但也有不尽完善的地方，因为如果我每个月的产品及规格不多即单月品种流量不大时，
我用分析函数做查询时速度是可以保障的。反之则会影响系统查询性能。
看了各位给出的其它SQL解决思路后，给我了一定的启发，
在此我很强调性能问题，因为我一张流水帐表一天有可能生成7k以上的记录。我在普通的机器测试13万条的数据，
用我的SQL需要0.13秒左右，如果同时在线的操作人员不算多的时候，用虚拟表速度应该还可以接受。