根據資金性質編碼、專案編碼分組 以年度、月份、單位預算編碼為條件查詢輸入月份的累計金額
?例如 查詢條件為 ：年份=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應該怎樣寫？感激不盡！
uj5u.com熱心網友回復：
……這個就是最基礎的
select a,b,c,sum(d),sum(e)
from 表名
where a=? and b=? and c=?
group by a,b,c;
陳述句吧
uj5u.com熱心網友回復：
select 年||月,sum(當月發生額) from group by 年||月||預算編碼看你的表格可以將年、月、預算編號拼接成一個欄位進行分組處理.
uj5u.com熱心網友回復：
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.經濟分類編碼
uj5u.com熱心網友回復：
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>
uj5u.com熱心網友回復：
目測是 財政行業。uj5u.com熱心網友回復：
上一個弄錯了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
uj5u.com熱心網友回復：
是這樣的嗎
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/96321.html
標籤：基礎和管理
上一篇：prime table sql