如何通过desc根据设备时间戳顺序从每个表中获取前1条记录

Question

我想获得每个表的最新设备时间戳数据，其中deviceimei="。我只获得一个基于最新时间戳降序的表记录，其他表没有根据设备时间戳降序获得最新记录。

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

Answer 1

这并不漂亮，但这里有一个解决方案。在上面的帖子中，到后一个表的联接不考虑它们的最大或最小时间戳，只考虑设备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