求累计金额,万分感谢

[erpcsu]

财务费用表ACTTB
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额)
2013 01 660202 30101 200
2013 02 660202 30101 500

2013 01 660208 30101 200
2013 02 660208 30101 100
2013 03 660208 30101 200
2013 04 660208 30101 200

2013 03 660207 30101 100
2013 04 660207 30101 500

比如现在要查询4月的费用,求如何实现
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额),BB(截止4月的累积)
2013 04 660202 30101 0 200+500
2013 04 660208 30101 200 200+100+200+200
2013 04 660207 30101 500 100+500

要查3月的费用显示为
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额),BB(截止3月的累积)
2013 03 660202 30101 0 200+500
2013 03 660208 30101 200 200+100+200
2013 03 660207 30101 100 100

主要是TB007(当月发生费用)和BB(截止当月发生的费用)的实现 谢谢

[叶子]

--> 测试数据：@ACTTB
declare @ACTTB table([TT001] int,[TT002] varchar(2),[TB005] int,[TB006] int,[TB007] int)
insert @ACTTB
select 2013,'01',660202,30101,200 union all
select 2013,'02',660202,30101,500 union all
select 2013,'01',660208,30101,200 union all
select 2013,'02',660208,30101,100 union all
select 2013,'03',660208,30101,200 union all
select 2013,'04',660208,30101,200 union all
select 2013,'03',660207,30101,100 union all
select 2013,'04',660207,30101,500

declare @month int set @month=4 --这个位置可以改成3
select 
	[TT001],@month as TT002,[TB005],sum([TB007]) as TB007
from @ACTTB
where [TT002]+0<=@month 
group by [TT001],[TB005]

[erpcsu]

不好意思楼上高手最好不要用@变量函数之类的,我是菜鸟.谢谢

[erpcsu]

最好用一个查询把要的信息查询出来就好 ,谢谢!!!

[唐诗三百首]

create table ACTTB
(TT001 varchar(10), TT002 varchar(10), TB005 varchar(10), TB006 varchar(10), TB007 int)

insert into ACTTB
select '2013', '01', '660202', '30101', 200 union all
select '2013', '02', '660202', '30101', 500 union all
select '2013', '01', '660208', '30101', 200 union all
select '2013', '02', '660208', '30101', 100 union all
select '2013', '03', '660208', '30101', 200 union all
select '2013', '04', '660208', '30101', 200 union all
select '2013', '03', '660207', '30101', 100 union all
select '2013', '04', '660207', '30101', 500


declare @tt002 varchar(10)
select @tt002='04'

select TT001,
       @tt002 'TT002',
       TB005,
       TB006,
       sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
       sum(TB007) 'BB'
 from ACTTB
 where TT002<=@tt002
 group by TT001,TB005,TB006

/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       04         660202     30101      0           700
2013       04         660207     30101      500         600
2013       04         660208     30101      200         700

(3 row(s) affected)
*/


declare @tt002 varchar(10)
select @tt002='03'

select TT001,
       @tt002 'TT002',
       TB005,
       TB006,
       sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
       sum(TB007) 'BB'
 from ACTTB
 where TT002<=@tt002
 group by TT001,TB005,TB006

/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       03         660202     30101      0           700
2013       03         660207     30101      100         100
2013       03         660208     30101      200         500

(3 row(s) affected)
*/

[erpcsu]

能不能SQL不用到变量, 用年和月做条件就可以查询出来. 这个是在ERP里面做一个报表,不知道如何使用这些变量 谢谢

[erpcsu]

如果是.NET程序我就会用到这些变量,可惜不是. 视图也不能用到变量

[daiyueqiang2045]

lz 你根据上边的思路，创建一个存储过程，将参数作为变量。查询你要的数据

[唐诗三百首]

用存储过程实现,前端程序访问存储过程,输入参数为月份,返回结果存为Recordset..

create table ACTTB
(TT001 varchar(10), TT002 varchar(10), TB005 varchar(10), TB006 varchar(10), TB007 int)
 
insert into ACTTB
select '2013', '01', '660202', '30101', 200 union all
select '2013', '02', '660202', '30101', 500 union all
select '2013', '01', '660208', '30101', 200 union all
select '2013', '02', '660208', '30101', 100 union all
select '2013', '03', '660208', '30101', 200 union all
select '2013', '04', '660208', '30101', 200 union all
select '2013', '03', '660207', '30101', 100 union all
select '2013', '04', '660207', '30101', 500
 
-- 创建存储过程
create proc sperp001
(@tt002 varchar(10))
as
begin
 set nocount on
  
 select TT001,
       @tt002 'TT002',
       TB005,
       TB006,
       sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
       sum(TB007) 'BB'
 from ACTTB
 where TT002<=@tt002
 group by TT001,TB005,TB006
end
go


-- 查询4月费用
exec sperp001 '04'
/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       04         660202     30101      0           700
2013       04         660207     30101      500         600
2013       04         660208     30101      200         700
*/

-- 查询3月费用
exec sperp001 '03'
/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       03         660202     30101      0           700
2013       03         660207     30101      100         100
2013       03         660208     30101      200         500
*/

[daiyueqiang2045]

引用 8 楼 ap0405140 的回复:

用存储过程实现,前端程序访问存储过程,输入参数为月份,返回结果存为Recordset..

create table ACTTB
(TT001 varchar(10), TT002 varchar(10), TB005 varchar(10), TB006 varchar(10), TB007 int)
 
insert into ACTTB
select '2013', '01', '660202', '30101', 200 union all
select '2013', '02', '660202', '30101', 500 union all
select '2013', '01', '660208', '30101', 200 union all
select '2013', '02', '660208', '30101', 100 union all
select '2013', '03', '660208', '30101', 200 union all
select '2013', '04', '660208', '30101', 200 union all
select '2013', '03', '660207', '30101', 100 union all
select '2013', '04', '660207', '30101', 500
 
-- 创建存储过程
create proc sperp001
(@tt002 varchar(10))
as
begin
 set nocount on
  
 select TT001,
       @tt002 'TT002',
       TB005,
       TB006,
       sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
       sum(TB007) 'BB'
 from ACTTB
 where TT002<=@tt002
 group by TT001,TB005,TB006
end
go


-- 查询4月费用
exec sperp001 '04'
/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       04         660202     30101      0           700
2013       04         660207     30101      500         600
2013       04         660208     30101      200         700
*/

-- 查询3月费用
exec sperp001 '03'
/*
TT001      TT002      TB005      TB006      TB007       BB
---------- ---------- ---------- ---------- ----------- -----------
2013       03         660202     30101      0           700
2013       03         660207     30101      100         100
2013       03         660208     30101      200         500
*/

OK