…… 这个就是最基础的 select a,b,c,sum(d),sum(e) from 表名 where a=? and b=? and c=? group by a,b,c; 语句吧
select 年||月,sum(当月发生额) from group by 年||月||预算编码看你的表格可以将年、月、预算编号拼接成一个字段进行分组处理.
select t.年度, t.月份, t.资金性质编码, t.项目编码, t.经济分类编码, t.当月发生额 from 表 t where t.年份=2017 and t.月份=2 and t.单位预算编码=318010 group by t.年度, t.月份, t.资金性质编码, t.项目编码, t.经济分类编码
SQL> SQL> create table test( 2 divid varchar(10), 3 m varchar(10), 4 AcctCode varchar(10), 5 econ varchar(10), 6 ApprMoney int 7 ); Table created SQL> begin 2 insert into test values(318001,1, '2190101','30101', 500); 3 insert into test values(318001,1, '2190101','30102', 500); 4 insert into test values(318001,1, '2190101','30103', 500); 5 insert into test values(318001,2, '2190101','30101', 400); 6 insert into test values(318001,2, '2190101','30102', 500); 7 insert into test values(318001,2, '2190101','30103', 600); 8 end; 9 / PL/SQL procedure successfully completed SQL> -- 方法 1 SQL> with m as ( 2 select divid, m, AcctCode, econ, 3 sum(ApprMoney) over(partition by divid, AcctCode, econ order by m) xx 4 from test 5 ) 6 select * from m where divid = '318001' and m = '2'; DIVID M ACCTCODE ECON XX ---------- ---------- ---------- ---------- ---------- 318001 2 2190101 30101 900 318001 2 2190101 30102 1000 318001 2 2190101 30103 1100 SQL> -- 方法 2 SQL> select divid, m, AcctCode, econ, 2 (select sum(ApprMoney) from test 3 where divid = t.divid and m <= t.m and AcctCode = t.AcctCode and econ = t.econ 4 ) x 5 from test t 6 where divid = '318001' and m = '2'; DIVID M ACCTCODE ECON X ---------- ---------- ---------- ---------- ---------- 318001 2 2190101 30101 900 318001 2 2190101 30102 1000 318001 2 2190101 30103 1100 SQL> drop table test purge; Table droppedSQL>
上一个弄错了 select t1.年度, t1.月份, t1.单位预算编码, t1.资金性质编码, t1.项目编码, t1.经济编码, t2.当月发生额 from 表 t1 left join (select t.项目编码, t.经济分类编码, sum(t.当月发生额) as 当月发生额 from 表 t where t.年份=2017 and t.月份<=2 and t.单位预算编码=318010 group by t.项目编码, t.经济分类编码) t2 on t1.项目编码=t2.项目编码 and t1.经济分类编码=t2.经济分类编码 where t1.年份=2017 and t1.月份=2 and t1.单位预算编码=318010
是这样的吗SELECT nd, yf, dwysbm, zjxzbm, xmbm, jjflbm, ljje FROM (SELECT t.*, SUM(dyfse) OVER(PARTITION BY zjxzbm,xmbm) ljje FROM t_zj t WHERE nd = '2017' AND dwysbm = '318010' AND yf <= 2) WHERE yf = 2
这个就是最基础的
select a,b,c,sum(d),sum(e)
from 表名
where a=? and b=? and c=?
group by a,b,c;
语句吧
t.月份,
t.资金性质编码,
t.项目编码,
t.经济分类编码,
t.当月发生额
from 表 t
where t.年份=2017 and t.月份=2 and t.单位预算编码=318010
group by t.年度,
t.月份,
t.资金性质编码,
t.项目编码,
t.经济分类编码
SQL>
SQL> create table test(
2 divid varchar(10),
3 m varchar(10),
4 AcctCode varchar(10),
5 econ varchar(10),
6 ApprMoney int
7 );
Table created
SQL> begin
2 insert into test values(318001,1, '2190101','30101', 500);
3 insert into test values(318001,1, '2190101','30102', 500);
4 insert into test values(318001,1, '2190101','30103', 500);
5 insert into test values(318001,2, '2190101','30101', 400);
6 insert into test values(318001,2, '2190101','30102', 500);
7 insert into test values(318001,2, '2190101','30103', 600);
8 end;
9 /
PL/SQL procedure successfully completed
SQL> -- 方法 1
SQL> with m as (
2 select divid, m, AcctCode, econ,
3 sum(ApprMoney) over(partition by divid, AcctCode, econ order by m) xx
4 from test
5 )
6 select * from m where divid = '318001' and m = '2';
DIVID M ACCTCODE ECON XX
---------- ---------- ---------- ---------- ----------
318001 2 2190101 30101 900
318001 2 2190101 30102 1000
318001 2 2190101 30103 1100
SQL> -- 方法 2
SQL> select divid, m, AcctCode, econ,
2 (select sum(ApprMoney) from test
3 where divid = t.divid and m <= t.m and AcctCode = t.AcctCode and econ = t.econ
4 ) x
5 from test t
6 where divid = '318001' and m = '2';
DIVID M ACCTCODE ECON X
---------- ---------- ---------- ---------- ----------
318001 2 2190101 30101 900
318001 2 2190101 30102 1000
318001 2 2190101 30103 1100
SQL> drop table test purge;
Table droppedSQL>
select t1.年度, t1.月份, t1.单位预算编码, t1.资金性质编码, t1.项目编码, t1.经济编码, t2.当月发生额 from 表 t1 left join
(select t.项目编码,
t.经济分类编码,
sum(t.当月发生额) as 当月发生额
from 表 t
where t.年份=2017 and t.月份<=2 and t.单位预算编码=318010
group by t.项目编码,
t.经济分类编码) t2 on t1.项目编码=t2.项目编码 and t1.经济分类编码=t2.经济分类编码
where t1.年份=2017 and t1.月份=2 and t1.单位预算编码=318010
FROM (SELECT t.*, SUM(dyfse) OVER(PARTITION BY zjxzbm,xmbm) ljje
FROM t_zj t
WHERE nd = '2017' AND dwysbm = '318010' AND yf <= 2) WHERE yf = 2