mssql sqlserver sqlserver 按年月对数据进行分组写法分享

转自:http://www.maomao365.com/?p=6841

摘要:
下文讲述三种sql脚本的写法，实现按年月对数据进行分类
实验环境:sql server 2008

---

实现思路:
方法1 :使用dateName获取年份和月份
方法2 :使用datepart获取年份和月份
方法3 :使用year month分别获取年份和月份　 

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