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


摘要:
下文讲述三种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 
mssql_sqlserver_按年月数据分组的三种方法分享

mssql_sqlserver_按年月数据分组的三种方法分享