-jcstone
declare @Readtime datetime
set @Readtime='2015-9-8'
print convert(nvarchar(30), getdate(),9)
SELECT A.[ID]
,A.[AmmeterID]
,A.[ReadTime]
,A.[ReadNumber]
,A.[Status]
,A.[Copyist]
FROM [Log_Ammeter] A left join ComAmmeter as b
on a.[AmmeterID] = b.ID
where A.[ReadTime]= (SELECT MAX([ReadTime]) FROM [Log_Ammeter]
where [ReadTime] <=@Readtime )
print convert(nvarchar(30), getdate(),9)
select * from
(select row_number() over(partition by AmmeterID order by [ReadTime] desc)as rownum,*
from [Log_Ammeter]) as T where T.rownum=1
print convert(nvarchar(30), getdate(),9)
select A.[ID]
,A.[AmmeterID]
,A.[ReadTime]
,A.[ReadNumber]
,A.[Status]
,A.[Copyist],* from [Log_Ammeter] A
inner join (select AmmeterID,max(ReadTime) as ReadTime from [Log_Ammeter] where ReadTime<=@ReadTime group by AmmeterID) B
on A.AmmeterID=B.AmmeterID and A.ReadTime =B.ReadTime
print convert(nvarchar(30), getdate(),9)
测试结果：
08 23 2016 7:56:14:163PM
(133 行受影响)
08 23 2016 7:56:16:900PM
(133 行受影响)
08 23 2016 7:56:17:397PM
显然，第一种方法耗时近三秒，后一种方法耗时仅0.5秒
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
