按年按月分组数据

先插入一些数据

create table test(qty int,info varchar(30),
writeDate datetime)
go
insert into test(qty,writeDate) values
(1,'2017-2-1'),(10,'2017-3-1'),(21,'2017-6-1'),(12,'2017-8-1'),
(31,'2018-1-1'),(41,'2018-2-1'),(51,'2018-2-1'),(61,'2018-2-1')

方法1:使用datename

SELECT datename(YEAR,writeDate)+'-'+datename(MONTH,writeDate) as newInfo,
sum(qty) as qtyAll
FROM test group by datename(YEAR,writeDate),datename(MONTH,writeDate)

方法2:使用datepart

SELECT CONVERT(varchar(5),datepart(YEAR,writeDate))+'-'+RIGHT('0'+convert(varchar(5),datepart(MONTH,writeDate)),2) as newinfo,
sum(qty) as qtyAll
FROM test group by datepart(YEAR,writeDate),datepart(MONTH,writeDate)

方法3：使用year month函数

SELECT CONVERT(varchar(5),YEAR(writeDate))+'-'+convert(varchar(5),MONTH(writeDate)) as newinfo,
sum(qty) as qtyAll
FROM test group by datepart(YEAR,writeDate),datepart(MONTH,writeDate)
go
truncate table test 
drop table test