如何根据desc的devicetimestamp顺序从每个表中获取前1条记录

debugcn 发表于 Dev

维诺德

我想获得每个表的最新devicetimestamp数据，其中deviceimei =''。im仅基于最新时间戳降序获得一个表记录，其余其他表则不基于设备时间戳降序获得最新记录。

sql

 select top 1 * from [TransTrak_V_1.0].[dbo].[Current_Voltage] as cv
      inner join [TransTrak_V_1.0].[dbo].[Overview]  as o
       on cv.DeviceImei = o.DeviceImei 
       inner join [TransTrak_V_1.0].[dbo].[Power]  as p
         on cv.DeviceImei = p.DeviceImei 
        inner join [TransTrak_V_1.0].[dbo].[PowerFactor]  as pf
        on cv.DeviceImei = pf.DeviceImei 
         inner join [TransTrak_V_1.0].[dbo].[Total_Power]  as tp
         on cv.DeviceImei = tp.DeviceImei
      where cv.DeviceImei='8673220311'
      order by cv.DeviceTimeStamp desc

罗斯·布什

这不是很漂亮，但这是一种解决方案。在上面的文章中，后面的表的联接不考虑它们的最大或最小时间戳，而只是设备ID。下面，已将联接转换为使用子查询，这些子查询返回按指定条件排序的每个表中的最高记录。表中将有没有匹配记录的空字段，非常类似于左联接。

select top 1 * from [TransTrak_V_1.0].[dbo].[Current_Voltage] as cv
inner join (
    Select top 1 * from [TransTrak_V_1.0].[dbo].[Overview] o 
    where cv.DeviceImei='8673220311'
    order by o.DeviceTimeStamp desc
) as o ON 1=1
inner join (
    Select top 1 * from [TransTrak_V_1.0].[dbo].[Power] p 
    where p.DeviceImei='8673220311'
    order by p.DeviceTimeStamp desc
) as p ON 1=1
inner join (
    Select top 1 * from [TransTrak_V_1.0].[dbo].[PowerFactor] pf 
    where pf.DeviceImei='8673220311'
    order by pf.DeviceTimeStamp desc
) as p ON 1=1  
inner join (
    Select top 1 * from [TransTrak_V_1.0].[dbo].[Total_Power] tp 
    where tp.DeviceImei='8673220311'
    order by tp.DeviceTimeStamp desc
) as tp ON 1=1  
where cv.DeviceImei='8673220311'
order by cv.DeviceTimeStamp desc

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。