SQL如何按年份月份统计？

select left(convert(varchar(10),[datetime],120),7) as 月份,sum(Amount) as 总数
from 表
where [datetime]<='你输入的时间条件'
group by left(convert(varchar(10),[datetime],120),7)

根据你的数据结果格式重新修改了下:

select cast(datepart(year,[datetime]) as varchar)+'年'+cast(datepart(month,[datetime]) as varchar)+'月' as 月份,sum(Amount) as 总数

from 表

where [datetime]<='你输入的时间条件' and datepart(year,[datetime])=left('你输入的时间条件',4)

group by cast(datepart(year,[datetime]) as varchar)+'年'+cast(datepart(month,[datetime]) as varchar)+'月'

如果datetime 是字符型

select left([datetime],7),sum(amount)
from tbl
where [datetime]<'2008-03-20'
group by left([datetime],7)
如果是datetime类型
left([datetime],7)换成datepart(month,[datetime])

条件按日期判断，然后用年月分组求和

SELECT EXTRACT(YEAR_MONTH(datetime)),SUM(Amount)
FROM TABLE
WHERE datetime<'2008-03-20'
GROUP BY EXTRACT(YEAR_MONTH(datetime))

declare @t table(id int, [datetime] datetime, amount int )
insert into @t
select 1,'2008-01-01',10 union
select 2,'2008-01-15',100 union
select 3,'2008-01-20',50 union
select 4,'2008-02-01',100 union
select 5,'2008-02-10',120 union
select 6,'2008-02-17',130 union
select 7,'2008-03-10',200 union
select 8,'2008-03-13',30 union
select 9,'2008-03-25',250

--select * from @t

select CONVERT(varchar(7),[datetime],120),SUM(amount)
from @t
where [datetime]<'2008-03-20'
group by CONVERT(varchar(7),[datetime],120)

--------------------
(9 row(s) affected)
yyyyMM sumAmount
------- -----------
2008-01 160
2008-02 350
2008-03 230

(3 row(s) affected)