SQL关于分组查询、累计求和问题

[qq_23292267]

根据资金性质编码、项目编码分组 以年度、月份、单位预算编码为条件查询输入月份的累计金额
​例如 查询条件为 ：年份=2017，月份=2，单位预算编码=318010
查询出来的结果集是
2017,2,318010,01,01,30212,4000
2017,2,318010,01,02,30215,1600
2017,2,318010,01,03,30217,2800

这个样子
请问各位大神SQL应该怎样写？感激不尽！

[小灰狼W]

…… 这个就是最基础的 select a,b,c,sum(d),sum(e) from 表名 where a=? and b=? and c=? group by a,b,c; 语句吧

[百炼成神 LV]

select 年||月,sum(当月发生额) from group by 年||月||预算编码 看你的表格可以将年、月、预算编号拼接成一个字段进行分组处理.

[gfs225]

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.经济分类编码

[卖水果的net]

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 dropped

SQL> 

[卖水果的net]

目测是 财政行业。

[gfs225]

上一个弄错了 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