不知下面能不能满足需求:create table A2(id1 int IDENTITY(1,1),日期 nvarchar(8),借 int,代 int,余 int)insert into A2 select '1月',500,200,900 union all select '1月',800,100,1600 union all select '2月',100,1500,200 select * from A2 a where id1>=( select max(id1) from A2 where 日期=a.日期)drop table A2结果: 2 1月 800 100 1600 3 2月 100 1500 200
用一个子查询: select 日期,sum(借),sum(代),sum(余) from(你的语句) a group by 日期
--试试 select 月=cast(month(t2.日期) as varchar) ,借1=isnull(sum(借),0) ,代1=isnull(sum(代),0) ,余额=sum(期初余额) +(select sum(isnull(借,0))-sum(isnull(代,0)) from A2 t1 where t1.id1<=t2.id1) from A2 t2 group by t2.日期
你是想从会计凭证上取数据生成总分类帐还是从你上面的明细分类帐生成哦 select t2.riqi,sum(t2.jf) as 借方,sum(t2.df) as 贷方,(select t1.yu_e from test t1 where t1.idl in (select max(idl) from test group by riqi) and t1.riqi=t2.riqi) as 余额 from test t2 group by t2.riqi可以得到结果你参考下吧 好运!
导出#T,然后Group by试试
select Month(t2.日期) AS iMonth, SUM(isnull(借,0)) as 借1, SUM(isnull(代,0)) as 代1, (期初余额+( select sum(isnull(借,0))-sum(isnull(代,0)) from A2 t1 where Year(t1.日期)<=Year(t2.日期) AND Month(t1.日期)<=Month(t2.日期) )) as 余额 from A2 t2 group by Year(t2.日期),Month(t2.日期)
像这种问题,能得出第一个表,也可以把它建成一个View,在View的基础上再计算!我测试的数据! ---------------------------------------------------------------------- create table Xiao ( id1 int not null identity(1,1), jf decimal(18,2) null, df decimal(18,2) null, riqi datetime null, yu_e decimal(18,2))insert into Xiao values (100,200,'2003-1-1',900) insert into Xiao values (800,100,'2003-1-2',1600) insert into Xiao values (100,1500,'2003-2-1',200) select Month(t2.riqi) AS iMonth, SUM(isnull(jf,0)) as 借1, SUM(isnull(df,0)) as 代1, (1000+( select sum(isnull(jf,0))-sum(isnull(df,0)) from Xiao t1 where Year(t1.riqi)<=Year(t2.riqi) AND Month(t1.riqi)<=Month(t2.riqi) )) as 余额 from Xiao t2 group by Year(t2.riqi),Month(t2.riqi)
2 1月 800 100 1600
3 2月 100 1500 200
select 日期,sum(借),sum(代),sum(余) from(你的语句) a group by 日期
结果应该是
日期 借方 贷方 余额
1000
1月 900 300 -200
2月 100 1500 -1600
报错时 t2 不包含在聚合函数中
请细心看看 那个t1.id1<=t2.id1 如何改写
或是有什么更好的方法写这个语句
select 月=cast(month(t2.日期) as varchar)
,借1=isnull(sum(借),0)
,代1=isnull(sum(代),0)
,余额=sum(期初余额)
+(select sum(isnull(借,0))-sum(isnull(代,0)) from A2 t1 where t1.id1<=t2.id1)
from A2 t2
group by t2.日期
jf float 8
df float 8
riqi datetime 8
yu_e float 8df 贷方 jf 借方 riqi 日期 yu_e 余额 id1 自增样式
日期 借 代 余
1000
1月 100 200 900
1月 800 100 1600
2月 100 1500 200 结果应该是
日期 借方 贷方 余额
1000
1月 900 300 1600
2月 100 1500 200先用余额=期初余额+借方-贷方 其中1000是我查出的期初余额
然后再用 余额=余额+借方-贷方
select t2.riqi,sum(t2.jf) as 借方,sum(t2.df) as 贷方,(select t1.yu_e from test t1 where t1.idl in (select max(idl) from test group by riqi) and t1.riqi=t2.riqi) as 余额 from test t2 group by t2.riqi可以得到结果你参考下吧 好运!
SUM(isnull(借,0)) as 借1,
SUM(isnull(代,0)) as 代1,
(期初余额+(
select sum(isnull(借,0))-sum(isnull(代,0)) from A2 t1
where Year(t1.日期)<=Year(t2.日期) AND Month(t1.日期)<=Month(t2.日期)
)) as 余额
from A2 t2
group by Year(t2.日期),Month(t2.日期)
像这种问题,能得出第一个表,也可以把它建成一个View,在View的基础上再计算!我测试的数据!
----------------------------------------------------------------------
create table Xiao
(
id1 int not null identity(1,1),
jf decimal(18,2) null,
df decimal(18,2) null,
riqi datetime null,
yu_e decimal(18,2))insert into Xiao values (100,200,'2003-1-1',900)
insert into Xiao values (800,100,'2003-1-2',1600)
insert into Xiao values (100,1500,'2003-2-1',200)
select Month(t2.riqi) AS iMonth,
SUM(isnull(jf,0)) as 借1,
SUM(isnull(df,0)) as 代1,
(1000+(
select sum(isnull(jf,0))-sum(isnull(df,0)) from Xiao t1
where Year(t1.riqi)<=Year(t2.riqi) AND Month(t1.riqi)<=Month(t2.riqi)
)) as 余额
from Xiao t2
group by Year(t2.riqi),Month(t2.riqi)