sql查询按时间累计 sql查询累加
\u5982\u4f55\u5728SQL\u4e2d\u6309\u65f6\u95f4\u6bb5\u67e5\u8be2\u6570\u636esql server\uff1a
select * from \u8868 where \u53d1\u751f\u65e5\u671f>'2008-7-1' and \u53d1\u751f\u65e5\u671f<'2008-12-31'
access\uff1a
select * from \u8868 where \u53d1\u751f\u65e5\u671f>#2008-7-1# and \u53d1\u751f\u65e5\u671f<#2008-12-31#
\u8fd9\u6837\u5c31\u53ef\u4ee5\u4e86\uff0c\u6ce8\u610fsql server\u4e0eaccess\u4e2d\u7684\u65e5\u671f\u6709\u4e00\u70b9\u4e0d\u4e00\u6837\u3002
\u6269\u5c55\u8d44\u6599\uff1asql\u67e5\u8be2\u65e5\u671f\u8bed\u53e5
select * from ShopOrder where datediff(week,ordTime,getdate()-1)=0 //\u67e5\u8be2\u5f53\u5929\u65e5\u671f\u5728\u4e00\u5468\u5e74\u7684\u6570\u636e
select * from ShopOrder where datediff(day,ordTime,getdate()-1)=0 //\u67e5\u8be2\u5f53\u5929\u7684\u6240\u6709\u6570\u636e
SELECT * FROM A where datediff(d,datetime,getdate()) <=30 //\u524d30\u5929
SELECT * FROM A WHERE DATEDIFF(m, shijian, GETDATE()) <=1 // \u4e0a\u4e00\u6708
\u67e5\u8be2\u5f53\u5929\u8bb0\u5f55\u53e6\u7c7b\u7684\u65b9\u6cd5\uff1a
SELECT *
FROM j_GradeShop
WHERE (GAddTime BETWEEN CONVERT(datetime, LEFT(GETDATE(), 10) + ' 00:00:00.000')
AND CONVERT(datetime, LEFT(GETDATE(), 10) + ' 00:00:00.000') + 1)
ORDER BY GAddTime DESC
\u5982\u679c\u53ea\u662f\u7b80\u5355\u7684\u7d2f\u52a0\u7684\u8bdd\uff1a
select sum(money) from a;
\u5982\u679c\u662f\u9010\u6761\u7d2f\u52a0\u7684\u8bdd\uff0c\u9ebb\u70e6\uff1a
\u7528sqlserver\u5199\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u4e2d\u95f4\u6709\u5f88\u591a\u77e5\u8bc6\uff0c\u597d\u597d\u5b66\u5b66\u5427\uff01
我用的是Oracle数据库，有些函数和写法可能数据库产品之间不太一样，没办法了。
首先创建一个表:
indexof Varchar2
dateof Date
feeof Number
然后插入测试数据：
insert all
into test08 values('A',to_date('2009-05-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),1)
into test08 values('B',to_date('2010-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),2)
into test08 values('A',to_date('2010-02-08 00:00:00','yyyy-mm-dd hh24:mi:ss'),3)
into test08 values('B',to_date('2010-03-09 00:00:00','yyyy-mm-dd hh24:mi:ss'),4)
into test08 values('A',to_date('2009-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),5)
into test08 values('A',to_date('2012-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),38)
select * from dual
然后这个表就和你那个差不多了
A 2009-05-02 00:00:00 1
B 2010-01-01 00:00:00 2
A 2010-02-08 00:00:00 3
B 2010-03-09 00:00:00 4
A 2009-03-01 00:00:00 5
A 2012-03-01 00:00:00 38
稍微多了几个主要是测试用。
然后查询你需要的：
select t1.indexof as indexof,t1.dateof as 日期,t1.feeof as 当期费用,
(
select sum(t2.feeof) from test08 t2
where t2.dateof<=t1.dateof
and t2.dateof>=trunc(t1.dateof,'yyyy')
and t2.indexof=t1.indexof
) as 本年度累加,
(
select sum(t2.feeof) from test08 t2
where t2.dateof<=t1.dateof
and t2.indexof=t1.indexof
) as 总累加
from test08 t1
order by dateof
注意：trunc函数是Oracle数据库函数，是取当前日期的年份的。
order by dateof如果不需要可以删除。有什么不明白的地方可以直接M我。
这样的数据放在EXCEL中处理比较好，SQL用来处理数据效果不好
绛旓細1> select from TEST_zfh521;2> go month value --- --- 1 6 2 10 3 9 (3 琛屽彈褰卞搷)1> SELECT 2> month,3> (SELECT SUM(value)FROM TEST_zfh521 as T WHERE t.month <= TEST_zfh521.month)AS all_val 4> FROM 5> TEST_zfh521 6> GROUP BY 7> month;8> go month all...
绛旓細sql璇彞涓猴細update琛╯et鏃ユ湡瀛楁=dateadd(m,1,鏃ユ湡瀛楁)瀹氫箟鍜岀敤娉旸ATEADD()鍑芥暟鍦ㄦ棩鏈熶腑娣诲姞鎴栧噺鍘绘寚瀹氱殑鏃堕棿闂撮殧銆傝娉旸ATEADD(datepart,number,date)date鍙傛暟鏄悎娉曠殑鏃ユ湡琛ㄨ揪寮忋俷umber鏄偍甯屾湜娣诲姞鐨勯棿闅旀暟锛涘浜庢湭鏉ョ殑鏃堕棿锛屾鏁版槸姝ｆ暟锛屽浜庤繃鍘荤殑鏃堕棿锛屾鏁版槸璐熸暟銆
绛旓細select 鏃ユ湡,sum(閲戦) 閲戦 from 琛ㄥ悕 group by 鏃ユ湡
绛旓細SELECT date, daily_production,SUM(daily_production) OVER (ORDER BY date) AS monthly_productionFROM productionORDER BY date;鍦ㄤ笂闈㈢殑鏌ヨ涓紝浣跨敤浜嗙獥鍙ｅ嚱鏁癝UM(daily_production) OVER (ORDER BY date)鏉ヨ绠楁瘡涓棩鏈熺殑鏈绱浜ч噺銆備娇鐢∣RDER BY date灏嗘暟鎹寜鏃ユ湡鎺掑簭锛屾渶鍚庝娇鐢⊿UM(daily_production...
绛旓細select a.鍗曚綅鍚嶇О,a.鏈湀閿閲,a.鏈湀閿鍞噾棰,b.鏈湀鍥炴閲戦,c.鏈勾绱閿鍞噺,c.鏈勾绱閿鍞噾棰,d.鏈勾绱鍥炴閲戦,e.鎬婚攢閲,e.鎬婚攢鍞噾棰,f.鎬诲洖娆鹃噾棰,g.骞村害鎬婚攢閲,g.骞村害鎬婚攢鍞噾棰,h.骞村害鎬诲洖娆鹃噾棰 from (select 1 as 缂栧彿,鍗曚綅鍚嶇О,sum(鏁伴噺) as 鏈湀閿閲,sum(閲戦) as...
绛旓細2> go month value --- --- 1 6 2 10 3 9 (3 琛屽彈褰卞搷)1> SELECT 2> month,3> (SELECT SUM(value) FROM TEST_zfh521 as T WHERE t.month <= TEST_zfh521.month) AS all_val 4> FROM 5> TEST_zfh521 6> GROUP BY 7> month;8> go month all...
绛旓細: :: :: :: :鍥犱负鏃ユ湡鏄己鐪佽瀹氱殑 浣犺鍋氱殑灏辨槸鍔犲叆涓浜涢噾棰 渚嬪瓙涓负浜嗙畝鍗曡捣瑙 鍋囪鍙湁涓涓摱琛岃处鎴(涓轰簡鏇寸鍚堝疄闄呮儏鍐 鍙互娣诲姞涓涓狟ankAccountNumber鍒)鐜板湪浣犲氨鑳藉鍒涘缓涓涓寘鎷簡褰撳墠浣欓鐨鏌ヨ 鍥犱负浣犲凡缁忓湪鍚屼竴鍒椾腑鐢ㄦ璐熸暟鍊艰褰曚簡瀛樻鍜屽彇娆惧 鎵浠ュ垪涓殑绱鍊兼槸寰堝鏄撶畻鍑虹殑 浣犺...
绛旓細鏇存柊搴旇涓嶅垎鍓嶅緦鐨勶紝鍙互杩欐牱锛歶pdate 琛ㄥ悕 set 绱 = (select sum(鏃ユ敹鍏) from 琛ㄥ悕 b where b.鏃ユ湡 <= 琛ㄥ悕.鏃ユ湡)
绛旓細select t1.*, (select sum(鏃ユ敹鍏ョ殑瀛楁) from (select * from 琛ㄥ悕 where 濮撳悕瀛楁=t1.濮撳悕瀛楁 and 鏃ユ湡瀛楁<=t1.鏃ユ湡瀛楁) ) as 绱鏀跺叆 from 琛ㄥ悕t1 浣犳妸涓婇潰鐨勬眽瀛楁浛鎹㈡垚浣犵殑琛ㄧ殑锛岃瘯璇曞簲璇ュ彲浠ヤ簡
绛旓細leij鍒楋紝涓绱鍊硷細璇彞瑙ｉ噴锛歷al---瀹氫箟鐨勫彉閲 val := 0---璧嬪煎彉閲忓垵濮嬪间负0 val:=A.val+@val ---姹傜疮璁″拰鍏紡 妗堜緥閲岋紝绗竴娆¤繍绠楋細A.dept = a ,@dept 涓虹┖,鏍规嵁涓婇潰鐨処F鍏紡锛屼袱鑰呬笉鐩哥瓑锛屽洜姝わ紝@val=A.val= 11,leij鍒楃涓涓间负11锛涙牴鎹彉閲忕殑鍐呴儴杩愮畻閫昏緫锛 鍦ㄤ竴涓細...
