按年是可以用这条的, select * from 表 where year(date)=2005但按月统计,由于你还要考虑到哪一年哪一个月, 所以不能完全用 select * from 表 where month(date)=1 应该还要加上年的条件 select * from 表 where month(date)=1 and year(date) = 2005 当然了 你也可以用时间段来选择使用的, select * from 表 where date >= 2005-01-01 and date < 2005-02-01
你是统计 money值 按年是可以用这条的, select sum(money) from 表 where year(date)=2005但按月统计,由于你还要考虑到哪一年哪一个月, 所以不能完全用 select sum(money)* from 表 where month(date)=1 应该还要加上年的条件 select sum(money) from 表 where month(date)=1 and year(date) = 2005 当然了 你也可以用时间段来选择使用的, select sum(money) from 表 where date >= 2005-01-01 and date < 2005-02-01
declare @a table(id int,name varchar(20),money int,date datetime) insert into @a select 1,'Corns',100,'2004-01-01 22:10:10' union select 2,'Corns',100,'2004-08-01 23:10:10' union select 3,'Corns',100,'2005-01-01 22:10:10' union select 4,'Corns',200,'2005-01-02 22:22:22' union select 5,'Corns',300,'2005-02-01 10:11:11' union select 6,'Corns',400, '2005-02-02 11:11:11'select a.id,a.name,a.money,a.date from @a a union all (select '','本月小计',sum(money),max(date) from @a group by month(date),year(date) ) union all (select '','本年合计',sum(money),stuff(convert(varchar(10),max(date),120),6,11,'12-31' ) from @a group by year(date)) order by date ,id desc--结果 --id name money date --1 Corns 100 2004-01-01 22:10:10.000 --0 本月小计 100 2004-01-01 22:10:10.000 --2 Corns 100 2004-08-01 23:10:10.000 --0 本月小计 100 2004-08-01 23:10:10.000 --0 本年合计 200 2004-12-31 00:00:00.000 --3 Corns 100 2005-01-01 22:10:10.000 --4 Corns 200 2005-01-02 22:22:22.000 --0 本月小计 300 2005-01-02 22:22:22.000 --5 Corns 300 2005-02-01 10:11:11.000 --6 Corns 400 2005-02-02 11:11:11.000 --0 本月小计 700 2005-02-02 11:11:11.000 --0 本年合计 1000 2005-12-31 00:00:00.000
select * from 表 where month(date)=1 应该还要加上年的条件
select * from 表 where month(date)=1 and year(date) = 2005 当然了 你也可以用时间段来选择使用的,
select * from 表 where date >= 2005-01-01 and date < 2005-02-01
select sum(money)* from 表 where month(date)=1 应该还要加上年的条件
select sum(money) from 表 where month(date)=1 and year(date) = 2005 当然了 你也可以用时间段来选择使用的,
select sum(money) from 表 where date >= 2005-01-01 and date < 2005-02-01
insert into @a
select 1,'Corns',100,'2004-01-01 22:10:10' union
select 2,'Corns',100,'2004-08-01 23:10:10' union
select 3,'Corns',100,'2005-01-01 22:10:10' union
select 4,'Corns',200,'2005-01-02 22:22:22' union
select 5,'Corns',300,'2005-02-01 10:11:11' union
select 6,'Corns',400, '2005-02-02 11:11:11'select a.id,a.name,a.money,a.date from @a a
union all
(select '','本月小计',sum(money),max(date) from @a group by month(date),year(date) )
union all
(select '','本年合计',sum(money),stuff(convert(varchar(10),max(date),120),6,11,'12-31' )
from @a group by year(date))
order by date ,id desc--结果
--id name money date
--1 Corns 100 2004-01-01 22:10:10.000
--0 本月小计 100 2004-01-01 22:10:10.000
--2 Corns 100 2004-08-01 23:10:10.000
--0 本月小计 100 2004-08-01 23:10:10.000
--0 本年合计 200 2004-12-31 00:00:00.000
--3 Corns 100 2005-01-01 22:10:10.000
--4 Corns 200 2005-01-02 22:22:22.000
--0 本月小计 300 2005-01-02 22:22:22.000
--5 Corns 300 2005-02-01 10:11:11.000
--6 Corns 400 2005-02-02 11:11:11.000
--0 本月小计 700 2005-02-02 11:11:11.000
--0 本年合计 1000 2005-12-31 00:00:00.000