create table PVAS.PZYQ_REPORT
(
JYRQ CHAR(8),
DLHJGM CHAR(8),
CZY CHAR(4),
PZZL VARCHAR2(200),
NUM_IN NUMBER,
NUM_OUT NUMBER,
TOTAL NUMBER
)
tablespace PVAS_USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on table PVAS.PZYQ_REPORT
is '空白凭证二次销号数据统计';
comment on column PVAS.PZYQ_REPORT.JYRQ
is '交易日期';
comment on column PVAS.PZYQ_REPORT.DLHJGM
is '代理行机构吗';
comment on column PVAS.PZYQ_REPORT.CZY
is '操作员';
comment on column PVAS.PZYQ_REPORT.PZZL
is '凭证种类';
comment on column PVAS.PZYQ_REPORT.NUM_IN
is '领入';
comment on column PVAS.PZYQ_REPORT.NUM_OUT
is '付出';
comment on column PVAS.PZYQ_REPORT.TOTAL
is '手动修改值';
要求按照jyrq,dlhjgm,czy,pzzl累加统计数据其中num_in表示凭证领用,num_out表示凭证使用,total表示校正值
num_in,num_out,total可能为空select t.jyrq,t.dlhjgm,t.czy,t.pzzl,sum(t.num_in+t.total-t.num_out) from
(select jyrq,dlhjgm,czy,pzzl,nvl(num_in,0) as num_in,nvl(num_out,0) as num_out,nvl(total,0) as total from pzyq_report order by jyrq,dlhjgm,czy,pzzl)t
where t.jyrq='20101101' and t.dlhjgm='32012511'
group by t.jyrq,t.dlhjgm,t.czy,t.pzzl知道的联系我,QQ:178070373,QQ技术群:16023628
(
JYRQ CHAR(8),
DLHJGM CHAR(8),
CZY CHAR(4),
PZZL VARCHAR2(200),
NUM_IN NUMBER,
NUM_OUT NUMBER,
TOTAL NUMBER
)
tablespace PVAS_USER01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on table PVAS.PZYQ_REPORT
is '空白凭证二次销号数据统计';
comment on column PVAS.PZYQ_REPORT.JYRQ
is '交易日期';
comment on column PVAS.PZYQ_REPORT.DLHJGM
is '代理行机构吗';
comment on column PVAS.PZYQ_REPORT.CZY
is '操作员';
comment on column PVAS.PZYQ_REPORT.PZZL
is '凭证种类';
comment on column PVAS.PZYQ_REPORT.NUM_IN
is '领入';
comment on column PVAS.PZYQ_REPORT.NUM_OUT
is '付出';
comment on column PVAS.PZYQ_REPORT.TOTAL
is '手动修改值';
要求按照jyrq,dlhjgm,czy,pzzl累加统计数据其中num_in表示凭证领用,num_out表示凭证使用,total表示校正值
num_in,num_out,total可能为空select t.jyrq,t.dlhjgm,t.czy,t.pzzl,sum(t.num_in+t.total-t.num_out) from
(select jyrq,dlhjgm,czy,pzzl,nvl(num_in,0) as num_in,nvl(num_out,0) as num_out,nvl(total,0) as total from pzyq_report order by jyrq,dlhjgm,czy,pzzl)t
where t.jyrq='20101101' and t.dlhjgm='32012511'
group by t.jyrq,t.dlhjgm,t.czy,t.pzzl知道的联系我,QQ:178070373,QQ技术群:16023628
直接使用cube就行了
http://qrqzhujinyi.javaeye.com/blog/616778
select t.jyrq, t.dlhjgm, t.czy, t.pzzl, sum(t.num_in + t.total - t.num_out)
from (select jyrq,
dlhjgm,
czy,
pzzl,
nvl(num_in, 0) as num_in,
nvl(num_out, 0) as num_out,
nvl(total, 0) as total
from pzyq_report
order by jyrq, dlhjgm, czy, pzzl) t
where t.jyrq = '20101101'
and t.dlhjgm = '32012511'
group by cube(t.jyrq, t.dlhjgm, t.czy, t.pzzl)
SQL> select deptno, empno,ename,sal,sum(sal)over(partition by deptno order by empno ) add_sal
2 from emp
3 ;
DEPTNO EMPNO ENAME SAL ADD_SAL
------ --------------------- ---------- --------- ----------
10 7782 CLARK 2964.50 2964.5
10 7839 KING 6050.00 9014.5
10 7934 MILLER 1573.00 10587.5
20 7369 SMITH 968.12 968.12
20 7566 JONES 3599.75 4567.87
20 7788 SCOTT 3630.00 8197.87
20 7876 ADAMS 1331.00 9528.87
20 7902 FORD 3630.00 13158.87
30 7499 ALLEN 1936.00 1936
30 7521 WARD 1512.50 3448.5
30 7654 MARTIN 1512.50 4961
30 7698 BLAKE 3448.50 8409.5
30 7844 TURNER 1815.00 10224.5
30 7900 JAMES 1149.50 11374
14 rows selected
SQL>
--直接这样就行了啊,没必要内层来做nvl处理:
select t.jyrq,
t.dlhjgm,
t.czy,
t.pzzl,
sum(nvl(t.num_in,0)+Nvl(t.total,0)-Nvl(t.num_out,0))
from pzyq_report t
where t.jyrq='20101101' and t.dlhjgm='32012511'
group by t.jyrq,t.dlhjgm,t.czy,t.pzzl
--上面分组写成这样,试试:
group by rollup(t.jyrq,t.dlhjgm,t.czy,t.pzzl)
t.dlhjgm,
t.czy,
t.pzzl这组值都会动态变化啊,20101031这天是最全的,以后的天数如果按照日期来说每天的记录水会不同啊,
如果按上面写的话,他只会显示每天的记录数啊,以前的记录就没有显示啊,也就是没实现累加啊
pzzl,这组值都不一样,但是需要汇总各类这种组合值的做个统计,这样查询到一天的话,就不准了,只能查看那天对应的组合信息数据了,其他的就没法看了,不知道说清楚了没,请指教select t.jyrq,t.dlhjgm,t.czy,t.pzzl,(t.t1+t.t2-t.t3) from
(select jyrq,dlhjgm,czy,pzzl,sum(nvl(num_in,0)) over (partition by dlhjgm,czy,pzzl order by jyrq,dlhjgm,czy,pzzl)as t1, sum(nvl(total,0)) over (partition by dlhjgm,czy,pzzl order by jyrq,dlhjgm,czy,pzzl)as t2,
sum(nvl(num_out,0)) over (partition by dlhjgm,czy,pzzl order by jyrq,dlhjgm,czy,pzzl)as t3
from pzyq_report )t where t.jyrq='20101103'
(select jyrq,dlhjgm,czy,pzzl,sum(nvl(num_in,0)) t1,
sum(nvl(total,0)) t2,
sum(nvl(num_out,0)) t3
from pzyq_report
group by jyrq,dlhjgm,czy,pzzl)t ,s_pzzl p
where $QueryClause
and trim(t.pzzl)=trim(p.pzzl2)
and t.jyrq>='20101031'
and t.jyrq<='$endDate'group by t.dlhjgm,t.czy,t.pzzl
如果也想把endDate前一天的余额统计出来,如何做好了