有请大力等高手：如何计算每日合计，每月合计，本年累计(直接用sql能不能实现，给个思路也行)

[hardworder]

类似财务账本的显示
create table stockin(fdate datetime,fitemname varchar(100),fqtyin decimal(18,2))
insert into stockin values('2003-1-3','AAA',125)
insert into stockin values('2003-1-10','AAA',120)
insert into stockin values('2003-1-13','AAA',-20)
insert into stockin values('2003-1-30','AAA',25)
insert into stockin values('2003-2-3','AAA',100)
insert into stockin values('2003-3-5','AAA',120)
能不能实现查询结果如下：
（结余数量根据上一笔结余加上第二笔发生额计算出来的）
（本日发生额按发生日期进行汇总得出）
（本月合计按月份的发生数量汇总得出）
（本年累计从1月到当前月份的月合计数量汇总）
日期 项 目 发生数量 结余数量
2003-1-3 AAA 125 125
2003-1-3 本日发生额 125 125
2003-1-10 AAA 120 245
2003-1-10 本日发生额 120 245
2003-1-13 AAA -20 225
2003-1-13 本日发生额 -20 225
2003-1-30 AAA 25 250
2003-1-30 本日发生额 25 250
本月合计 250 250
本年累计 250 250
2003-2-3 AAA 100 350
2003-2-3 本日发生额 100 350
本月合计 100 350
本年累计 350 350
2003-3-5 AAA 120 470
2003-3-5 本日发生额 120 470
本月合计 120 470
本年累计 470 470

[pengdali]

create table #stockin(fdate datetime,fitemname varchar(100),fqtyin decimal(18,2))
insert into #stockin values('2003-1-3','AAA',125)
insert into #stockin values('2003-1-10','AAA',120)
insert into #stockin values('2003-1-13','AAA',-20)
insert into #stockin values('2003-1-30','AAA',25)
insert into #stockin values('2003-2-3','AAA',100)
insert into #stockin values('2003-3-5','AAA',120)

select fdate,fitemname,fqtyin,结余数量 from(
select CONVERT(varchar(10),fdate,120) fdate,fitemname,fqtyin,(select sum(fqtyin) from #stockin where fdate<=tem.fdate) 结余数量,CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from #stockin tem
union all
select CONVERT(varchar(10),fdate,120),fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(day,fdate,tem.fdate)>=0),CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from (select CONVERT(varchar(10),fdate,120) fdate,'本日发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(varchar(10),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(month,fdate,tem.fdate+'-01')>=0),fdate+'-32',fdate,left(fdate,4) from (select CONVERT(char(7),fdate,120) fdate,'本月发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(7),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(year,fdate,tem.fdate+'-01-01')>=0),fdate+'-13-33',fdate+'-13',fdate from (select CONVERT(char(4),fdate,120) fdate,'本年累计额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(4),fdate,120)) tem) tem2
order by flag3,flag2,flag1

[hardworder]

多谢回答,但我发现两位的回答都无法达到效果,如果一天出现多笔业务,余额肯定是有问题的,不过思路很好,最起码我知道可以用几句sql就可实现.

[DragonBill]

--建表
create table stockin(
fdate datetime,
fitemname varchar(100),
fqtyin decimal(18,2)
)
--加字段
alter table stockin add fmonth datetime
--建资料
insert into stockin values('2003-1-3','AAA',125,'2003-1-31 23:59:59')
insert into stockin values('2003-1-10','AAA',120,'2003-1-31 23:59:59')
insert into stockin values('2003-1-13','AAA',-20,'2003-1-31 23:59:59')
insert into stockin values('2003-1-30','AAA',25,'2003-1-31 23:59:59')
insert into stockin values('2003-2-3','AAA',100,'2003-2-28 23:59:59')
insert into stockin values('2003-3-5','AAA',120,'2003-3-31 23:59:59')
--我查
select fdate 日期, fitemname 项目, fqtyin 发生数量, --挑日常资料
(select sum(fqtyin) from stockin where fdate<=s1.fdate) 结余数量
from stockin s1
union
select fdate 日期, '本日发生额' 项目, fqtyin 发生数量, --挑日结资料
(select sum(fqtyin) from stockin where fdate<=s2.fdate) 结余数量
from stockin s2
union
select fmonth 月份, '本月合计' 项目,sum(fqtyin) 发生数量, --挑月结资料
(select sum(fqtyin)
from
(select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm
where fmonth<=s3.fmonth) 结余数量
from stockin s3
group by fmonth
union
select fmonth 月份, '本年累计' 项目, --年结资料
(select sum(fqtyin)
from (select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm
where fmonth<=s4.fmonth) 发生数量,
(select sum(fqtyin) from stockin where fdate<=s4.fmonth) 结余数量
from stockin s4
group by fmonth

--说明
1.建一辅助字段 fmonth,其用意看看挑本月合计和本年累计就知道了,其值定义很精确用于排序
2.本年累计和本月合计顺序不对，将就着用吧
3.可能不用辅助字段fmonth也行，太晚了想睡觉，no thing...

[vvyjp]

学习学习