表1
F_id F_name F_num F_date
1 aaa 10 2006-02-01
2 bbb 10 2006-06-01
3 ccc 10 2006-06-02
4 ddd 10 2006-06-06
5 eee 10 2006-06-06 统计出
本月的合计,本年的合计
结果如下:]本月合计 40
本年合计 50
F_id F_name F_num F_date
1 aaa 10 2006-02-01
2 bbb 10 2006-06-01
3 ccc 10 2006-06-02
4 ddd 10 2006-06-06
5 eee 10 2006-06-06 统计出
本月的合计,本年的合计
结果如下:]本月合计 40
本年合计 50
本年合计=SUM(CASE WHEN DATEDIFF(YEAR,A.F_date,GETDATE())=0 THEN F_num ELSE 0 END)
FROM 表 A
本月合计 As N'合计',
SUM(F_num) As [SUM]
From 表1
Where Month(F_date)=Month(GetDate())
Union All
Select
本年合计 As N'合计',
SUM(F_num) As [SUM]
From 表1
Where Year(F_date)=Year(GetDate())
declare @AA table(F_id int,F_name varchar(20),F_num int,F_date datetime)insert @AA values(1,'aaa',10,'2006-02-01')
insert @AA values(2,'bbb',10,'2006-06-01')
insert @AA values(3,'ccc',10,'2006-06-02')
insert @AA values(4,'ddd',10,'2006-06-06')
insert @AA values(5,'eee',10,'2006-06-06')
select * from @AA--本月
select sum(F_num) from @AA where datepart(month,F_date)=datepart(month,getdate())--本年select sum(F_num) from @AA where datepart(year,F_date)=datepart(year,getdate())
insert tab values(1,'aaa',10,'2006-02-01')
insert tab values(2,'bbb',10,'2006-06-01')
insert tab values(3,'ccc',10,'2006-06-02')
insert tab values(4,'ddd',10,'2006-06-06')
insert tab values(5,'eee',10,'2006-06-06')
本月 select sum(f_num) as sum from tab where month(f_date) = 06 group by month(f_date)
本年 select sum(f_num) as sum from tab where year(f_date) = 2006 group by year(f_date)