sql语句比较两个月的数据？

显示全部楼层 · 2009-8-30 13:14:36

select iif(isnull(sum(收入金额)),0,sum(收入金额)) as 收入金额,iif(isnull(sum(支出金额)),0,sum(支出金额)) as 支出金额,iif(isnull(sum(收入金额)),0,sum(收入金额))-iif(isnull(sum(支出金额)),0,sum(支出金额)) as 余额 from 收支表 where year(日期)=2009 and month(日期)=(select max(month(日期))-1 from 收支表)以上，希望对你有所帮助！

千问 · 2009-8-30 13:14:36

补充回答：这样的确简单很多给你sqlselect sum(income) as incomes,sum(payment) as payments, concat(year(createtime),'-',month(createtime)) as month from test group bymonth order by month desc limit 2 我这里面的函数是MYSQL里面的，如果是SQLSERVER,你自己需要换下

千问 · 2009-8-30 13:14:36

本年内的查询，只算出每个月的收入和支出总金额，至于你需要的自己可以算下！select sum(收入金额) as 总收入金额,sum(支出金额 ) as 总支出金额，month(日期) from 收支表 group by month(日期)

千问 · 2009-8-30 13:14:36

--创建表create table test( daytime datetime ,earn int,buy int )--插入表内容insert into test values ('2009-8-20', 3000, 200)insert into test values ('2009-8-26', 500,1000)insert into test values ('2009-7-10', 2000,400)insert into test values ('2009-7-11', 1500,100)insert into test values ('2009-6-22', 200,500)select * from test--结果六月和七月对比selecta.六月收入,
b.七月收入,
( b.七月收入-a.六月收入/a.六月收入) 收入增长,
a.六月支出,
b.七月支出,
(b.七月支出-a.六月支出)/a.六月支出支出增长
from (select sum(earn) 七月收入,
sum(buy) 七月支出fromtest where convert(varchar(6),daytime,112)=200907) b,
( select sum(test.earn) 六月收入,
sum(test.buy)六月支出
fromtest
where convert(varchar(6),test.daytime,112)=200906
) adatediff()这个函数是用来得到时间差的,查查吧,