SQL 按时间（年月日）分组获取每组最早第一条记录

declare @WatermeterID int   

declare @BeginTime datetime 

declare @EndTime datetime 

set @WatermeterID=108

set @BeginTime='2016-1-1'

set @EndTime='2018-12-12'

----每年最早第一条读数

select * from 

(select WatermeterID, ReadDatetime, ReadNumber ,ROW_NUMBER() 

over (partition by  WatermeterID,(Datename(year,ReadDatetime))  

order by ReadDatetime) as NEWINDEX

from [Log_Water_WaterMeter])t where t.NEWINDEX =1 

and [WatermeterID] = @WatermeterID and ([ReadDatetime] between @BeginTime and @EndTime ) 

 order by ReadDatetime 

----每月最早第一条读数

select * from 

(select WatermeterID, ReadDatetime, ReadNumber ,ROW_NUMBER() 

over (partition by  WatermeterID,(Datename(year,ReadDatetime)+Datename(month,ReadDatetime))  

order by ReadDatetime) as NEWINDEX

from [Log_Water_WaterMeter])t where t.NEWINDEX =1 

and [WatermeterID] = @WatermeterID and ([ReadDatetime] between @BeginTime and @EndTime ) 

 order by ReadDatetime 

--每天最早第一条读数

 select * from 

(select WatermeterID, ReadDatetime, ReadNumber ,ROW_NUMBER() 

over (partition by  WatermeterID,(Datename(year,ReadDatetime)+Datename(month,ReadDatetime)+Datename(day,ReadDatetime))  

order by ReadDatetime) as NEWINDEX

from [Log_Water_WaterMeter])t where t.NEWINDEX =1 

and [WatermeterID] = @WatermeterID and ([ReadDatetime] between @BeginTime and @EndTime ) 

 order by ReadDatetime 

时间3秒左右

分组分别为年、年月、年月日

分组日期方法采用Datename：

Datename(year,ReadDatetime)+Datename(month,ReadDatetime)+Datename(day,ReadDatetime)

也可以采用CONVERT日期转换，但DateName速度似乎更快

CONVERT：

年：CONVERT(VarChar(4), getdate(), 112)   

年月：CONVERT(VarChar(6), getdate(), 112)   

年月日：：CONVERT(VarChar(8), getdate(), 112)