SQL Union获取符合条件的第一条和最后一条记录

declare @BuildingID int  

declare @TimeUnit int  

declare @BeginTime datetime 

declare @EndTime datetime 

set @BuildingID=1

set @BeginTime='2018-1-1'

set @EndTime='2018-8-10'

select GETDATE()

select * from (

    SELECT   A.[ID]

      ,A.[WatermeterID]

      ,A.[ReadTime]

      ,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber] 

FROM [Log_Watermeter] A 

inner join (select [WatermeterID],min([ReadTime]) as [ReadTime] from [Log_Watermeter] 

where  [ReadTime]>=@BeginTime group by WatermeterID) B 

on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]

left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID 

where    C.BuildingID  = @BuildingID  ) t1

Union

select * from (

   SELECT   A.[ID]

  ,A.[WatermeterID]

  ,A.[ReadTime]

  ,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber] 

FROM [Log_Watermeter] A 

inner join (select [WatermeterID],max([ReadTime]) as [ReadTime] from [Log_Watermeter] 

where  [ReadTime]<=@EndTime group by WatermeterID) B 

on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]

left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID 

where    C.BuildingID  = @BuildingID  ) t2

实际为 开始时间对应的数值应向前而不是向后取值比较合理，特别是值按时间增长的读数记录。因此改为：

select * from (

    SELECT   A.[ID]

      ,A.[WatermeterID]

      ,A.[ReadTime]

      ,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber] 

FROM [Log_Watermeter] A 

inner join (select [WatermeterID],Max([ReadTime]) as [ReadTime] from [Log_Watermeter] 

where  [ReadTime]<=@BeginTime group by WatermeterID) B 

on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]

left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID 

where    C.BuildingID  = @BuildingID  ) t1

Union

select * from (

   SELECT   A.[ID]

  ,A.[WatermeterID]

  ,A.[ReadTime]

  ,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber] 

FROM [Log_Watermeter] A 

inner join (select [WatermeterID],max([ReadTime]) as [ReadTime] from [Log_Watermeter] 

where  [ReadTime]<=@EndTime group by WatermeterID) B 

on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]

left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID 

where    C.BuildingID  = @BuildingID  ) t2