如何查询最近3个月的发生金额

bnd248 2009-03-17 02:55:37
因为管理的需要需以3个月为期得到月发生金额


原表:
年 月 科目代码 客户代码 期初余额 本月借方发生 本月贷方发生 期末余额
2005 4 122 01.053.00173 0.00 416820.00 416820.00 0.00
2005 5 122 01.053.00173 0.00 351000.00 0.00 351000.00
2005 6 122 01.053.00173 351000.00 8500.00 351000.00 8500.00
2005 7 122 01.053.00173 8500.00 780000.00 788500.00 0.00
2005 8 122 01.053.00173 0.00 1964810.00 194250.00 1770560.00
2005 9 122 01.053.00173 1770560.00 37000.00 792000.00 1015560.00
2005 10 122 01.053.00173 1015560.00 0.00 1015560.00 0.00
2005 11 122 01.053.00239 0.00 0.00 0.00 0.00
2005 12 122 01.053.00239 0.00 0.00 0.00 0.00
2006 1 122 01.053.00239 0.00 0.00 0.00 0.00
2006 2 122 01.053.00239 0.00 0.00 0.00 0.00
2006 3 122 01.053.00239 0.00 0.00 0.00 0.00
2006 4 122 01.053.00239 0.00 1807110.00 0.00 1807110.00
2006 5 122 01.053.00239 1807110.00 1346400.00 1330560.00 1822950.00
2006 6 122 01.053.00239 1822950.00 0.00 1346400.00 476550.00
2006 7 122 01.053.00239 476550.00 0.00 476550.00 0.00
2006 8 122 01.053.00239 0.00 0.00 0.00 0.00
2006 9 122 01.053.00239 0.00 0.00 0.00 0.00

结果表:
年 月 科目代码 客户代码 3个月前期初余额 近3个月借方发生 近3个月贷方发生 期末余额
2005 4 122 01.053.00173 0.00 416820.00 416820.00 0.00
2005 5 122 01.053.00173 0.00 767820.00 416820.00 351000.00
2005 6 122 01.053.00173 0.00 776320.00 767820.00 8500.00
2005 7 122 01.053.00173 0.00 1139500.00 1139500.00 0.00
2005 8 122 01.053.00173 351000.00 2753310.00 1333750.00 1770560.00
2005 9 122 01.053.00173 8500.00 2781810.00 1774750.00 1015560.00
2005 10 122 01.053.00173 1015560.00 0.00 1015560.00 0.00
2005 11 122 01.053.00239 0.00 0.00 0.00 0.00
2005 12 122 01.053.00239 0.00 0.00 0.00 0.00
2006 1 122 01.053.00239 0.00 0.00 0.00 0.00
2006 2 122 01.053.00239 0.00 0.00 0.00 0.00
2006 3 122 01.053.00239 0.00 0.00 0.00 0.00
2006 4 122 01.053.00239 0.00 1807110.00 0.00 1807110.00
2006 5 122 01.053.00239 0.00 3153510.00 1330560.00 1822950.00
2006 6 122 01.053.00239 0.00 3153510.00 2676960.00 476550.00
2006 7 122 01.053.00239 1807110.00 1346400.00 3153510.00 0.00
2006 8 122 01.053.00239 1822950.00 0.00 1822950.00 0.00
2006 9 122 01.053.00239 476550.00 0.00 476550.00 0.00


...全文
123 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
wfu_liuxh 2009-03-21
  • 打赏
  • 举报
 回复
学习了，牛。
claro 2009-03-21
  • 打赏
  • 举报
 回复
学习6楼的，实现了，牛。
百年树人 2009-03-21
  • 打赏
  • 举报
 回复 
---测试数据---

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([年] int,[月] int,[科目代码] int,[客户代码] varchar(12),[期初余额] numeric(9,2),[本月借方发生] numeric(9,2),[本月贷方发生] numeric(9,2),[期末余额] numeric(9,2))

insert [tb]

select 2005,4,122,'01.053.00173',0.00,416820.00,416820.00,0.00 union all

select 2005,5,122,'01.053.00173',0.00,351000.00,0.00,351000.00 union all

select 2005,6,122,'01.053.00173',351000.00,8500.00,351000.00,8500.00 union all

select 2005,7,122,'01.053.00173',8500.00,780000.00,788500.00,0.00 union all

select 2005,8,122,'01.053.00173',0.00,1964810.00,194250.00,1770560.00 union all

select 2005,9,122,'01.053.00173',1770560.00,37000.00,792000.00,1015560.00 union all

select 2005,10,122,'01.053.00173',1015560.00,0.00,1015560.00,0.00 union all

select 2005,11,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2005,12,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2006,1,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2006,2,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2006,3,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2006,4,122,'01.053.00239',0.00,1807110.00,0.00,1807110.00 union all

select 2006,5,122,'01.053.00239',1807110.00,1346400.00,1330560.00,1822950.00 union all

select 2006,6,122,'01.053.00239',1822950.00,0.00,1346400.00,476550.00 union all

select 2006,7,122,'01.053.00239',476550.00,0.00,476550.00,0.00 union all

select 2006,8,122,'01.053.00239',0.00,0.00,0.00,0.00 union all

select 2006,9,122,'01.053.00239',0.00,0.00,0.00,0.00

 

---查询---

select * into #

from

(

  select 

    px1=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))/3+1,

    px2=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))%3+1,

    px3=(select count(1) from tb where ltrim(年)+right('00'+ltrim(月),2)<ltrim(t.年)+right('00'+ltrim(t.月),2))+1,

    * 

  from tb t

) tt



select *from #



select 

  年,月,科目代码,客户代码,

  [3个月前期初余额]=isnull((select 期末余额 from # where px2=t.px2 and px1=t.px1-1),0),

  [近3个月借方发生]=isnull((select sum(本月借方发生) from # where px1=t.px1 and px3<=t.px3),0),

  [近3个月贷方发生]=isnull((select sum(本月贷方发生) from # where px1=t.px1 and px3<=t.px3),0),

  期末余额

from # t 



drop table #



---结果---

年           月           科目代码        客户代码         3个月前期初余额    近3个月借方发生                                 近3个月贷方发生                                 期末余额        

----------- ----------- ----------- ------------ ----------- ---------------------------------------- ---------------------------------------- ----------- 

2005        4           122         01.053.00173 .00         416820.00                                416820.00                                .00

2005        5           122         01.053.00173 .00         767820.00                                416820.00                                351000.00

2005        6           122         01.053.00173 .00         776320.00                                767820.00                                8500.00

2005        7           122         01.053.00173 .00         780000.00                                788500.00                                .00

2005        8           122         01.053.00173 351000.00   2744810.00                               982750.00                                1770560.00

2005        9           122         01.053.00173 8500.00     2781810.00                               1774750.00                               1015560.00

2005        10          122         01.053.00173 .00         .00                                      1015560.00                               .00

2005        11          122         01.053.00239 1770560.00  .00                                      1015560.00                               .00

2005        12          122         01.053.00239 1015560.00  .00                                      1015560.00                               .00

2006        1           122         01.053.00239 .00         .00                                      .00                                      .00

2006        2           122         01.053.00239 .00         .00                                      .00                                      .00

2006        3           122         01.053.00239 .00         .00                                      .00                                      .00

2006        4           122         01.053.00239 .00         1807110.00                               .00                                      1807110.00

2006        5           122         01.053.00239 .00         3153510.00                               1330560.00                               1822950.00

2006        6           122         01.053.00239 .00         3153510.00                               2676960.00                               476550.00

2006        7           122         01.053.00239 1807110.00  .00                                      476550.00                                .00

2006        8           122         01.053.00239 1822950.00  .00                                      476550.00                                .00

2006        9           122         01.053.00239 476550.00   .00                                      476550.00                                .00



（所影响的行数为 18 行）
ChinaJiaBing 2009-03-21
  • 打赏
  • 举报
 回复 


select 年,科目代码,客户代码,SUM(期初余额),SUM(本月借方发生),sum(本月贷方发生),SUM(期末余额)

         from 表 group by 年,datepart( quarter,(年+'-' +月 + '-'+ '00')),科目代码,客户代码
-狙击手- 2009-03-21
  • 打赏
  • 举报
 回复
select 年,
月,
科目代码,
客户代码,
3个月前期初余额 = (select 期初余额 from ta where datediff(mm,ltrim(年)+'-'+ltrim(月)+'01',ltrim(a.年)+'-'+ltrim(a.月)+'01') = 3 ) ,
近3个月借方发生= ...
近3个月贷方发生= ...
期末余额
from ta a
htl258_Tony 2009-03-21
  • 打赏
  • 举报
 回复
这么多,楼主分也给得太少了吧,其它贴子先来了.
claro 2009-03-21
  • 打赏
  • 举报
 回复
帮提供数据
Declare @a Table (年 varchar(20), 月 varchar(20),科目代码 varchar(20), 客户代码 varchar(20),

期初余额 float, 本月借方发生 float,本月贷方发生 float,期末余额 float)

Insert @a Select

'2005','4','122','01.053.00173',0.00,416820.00,416820.00,0.00 Union All Select

'2005','5','122','01.053.00173',0.00,351000.00,0.00,351000.00 Union All Select

'2005','6','122','01.053.00173',351000.00,8500.00,351000.00,8500.00 Union All Select

'2005','7','122','01.053.00173',8500.00,780000.00,788500.00,0.00 Union All Select

'2005','8','122','01.053.00173',0.00,1964810.00,194250.00,1770560.00 Union All Select

'2005','9','122','01.053.00173',1770560.00,37000.00,792000.00,1015560.00 Union All Select

'2005','10','122','01.053.00173',1015560.00,0.00,1015560.00,0.00 Union All Select

'2005','11','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2005','12','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2006','1','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2006','2','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2006','3','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2006','4','122','01.053.00239',0.00,1807110.00,0.00,1807110.00 Union All Select

'2006','5','122','01.053.00239',1807110.00,1346400.00,1330560.00,1822950.00 Union All Select

'2006','6','122','01.053.00239',1822950.00,0.00,1346400.00,476550.00 Union All Select

'2006','7','122','01.053.00239',476550.00,0.00,476550.00,0.00 Union All Select

'2006','8','122','01.053.00239',0.00,0.00,0.00,0.00 Union All Select

'2006','9','122','01.053.00239',0.00,0.00,0.00,0.00



select * from @a

年	月	科目代码	客户代码	期初余额	本月借方发生	本月贷方发生	期末余额

2005	4	122	01.053.00173	0	416820	416820	0

2005	5	122	01.053.00173	0	351000	0	351000

2005	6	122	01.053.00173	351000	8500	351000	8500

2005	7	122	01.053.00173	8500	780000	788500	0

2005	8	122	01.053.00173	0	1964810	194250	1770560

2005	9	122	01.053.00173	1770560	37000	792000	1015560

2005	10	122	01.053.00173	1015560	0	1015560	0

2005	11	122	01.053.00239	0	0	0	0

2005	12	122	01.053.00239	0	0	0	0

2006	1	122	01.053.00239	0	0	0	0

2006	2	122	01.053.00239	0	0	0	0

2006	3	122	01.053.00239	0	0	0	0

2006	4	122	01.053.00239	0	1807110	0	1807110

2006	5	122	01.053.00239	1807110	1346400	1330560	1822950

2006	6	122	01.053.00239	1822950	0	1346400	476550

2006	7	122	01.053.00239	476550	0	476550	0

2006	8	122	01.053.00239	0	0	0	0

2006	9	122	01.053.00239	0	0	0	0
claro 2009-03-21
  • 打赏
  • 举报
 回复
帮顶
通过程序实现吧。
sqlalchemy-challenge:SQLAlchemyHomework-冲浪！
SQLAlchemyHomework-冲浪！ 步骤1-气候分析与探索 降水分析 使用该日期，通过查询前12个月的数据来检索最近12个月的降水数据。 降水数据汇总统计 车站分析 从最低，最高，平均查询中最活跃的电台 设计查询以检索最近12个月的温度观测数据（TOBS）
mysql 查询每个人、每个月的消费金额及每年的消费总额
mysql 查询每个人、每个月的消费金额及每年的消费总额 最近按需求实现会员年报表， 假设现有会员表member、订单表i_order， member表字段(id, member_name, xxxx）, i_order表字段(id,member_id,handle_time,money,order_status,xxxx); 思路如下： 分别查询12个月及全年各会员消费总额，得到13个表，再...
mysql查询最近12个月、最近7天、最近30天、每天、昨天、上个月的记录
mysql查询最近12个月、最近7天、最近30天、每天、昨天、上个月的记录 1、查询最近12个月的数据（对查询出的数据用having条件做过滤） 先上结果： DATABASE_TIME < DATE_FORMAT( DATE_ADD( NOW( ), INTERVAL 1 MONTH ), ‘%Y-%m’ ) AND DATABASE_TIME > DATE_FORMAT( DATE_ADD( NOW( ), INTERVAL - 12 MONTH ), ‘%Y-%m’ ) eg： SELECT
Mysql 查询统计最近12个月的数据
Mysql 查询统计最近12个月的数据
mysql 查询最近6个月的数据（每个月的数据，没有数据的为0）
mysql 查询最近6个月的数据（每个月的数据，没有数据的为0）
疑难问题

22,181

社区成员

121,573

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章