SQL选择多个值存储过程

本文关键字：存储过程 选择 SQL | 更新日期: 2023-09-27 18:27:39

我有两个表：设备详细信息：具有每个设备的详细信息其他表设备记录。

我想在设备列表上做出选择声明，以从设备详细信息中获得每个设备的最高记录：

SELECT TOP 1
       [t].[DeviceNumber]
     , [d].[CarNumber] AS [CarNumber]
     , [t].TimeStamp
FROM [DeviceRecords] AS [t]
LEFT JOIN [Device_Details] AS [d]
    ON [t].[DeviceNumber] = [d].[AVL_DeviceNumber]
WHERE [t].[DeviceNumber] IN ('013950003919546', '358244017672256', '013950003919876', '013227005026319', '013226001974274') 
ORDER BY
         [t].TimeStamp DESC

我没有在C#中单独执行For Loop和查询每个设备，而是在思考如何在一次查询中完成，该查询返回每个设备的TOP记录。

SQL选择多个值存储过程

当您可以使用APPLY:时就是这种情况

select t.DeviceNumber, 
       oa.CarNumber, 
       t.TimeStamp
from [DeviceRecords] as t
outer apply(select top 1 CarNumber 
            from [Device_Details] as d 
            where t.DeviceNumber = d.AVL_DeviceNumber 
            order by ... desc) oa   
where t.DeviceNumber in ('013950003919546', '358244017672256',
                         '013950003919876', '013227005026319', '013226001974274')

您应该将...替换为详细信息表中相应的列。

尝试使用子查询

SELECT TOP 1
   [t].[DeviceNumber]
 , [d].[CarNumber] AS [CarNumber]
 , [t].TimeStamp
FROM (Select [i].[DeviceNumber], MAX([i].[TimeStamp]) as 'TimeStamp'
      from [DeviceRecords] AS [i]
      group by [i].[DeviceNumber]) AS [t]
LEFT JOIN AS [d]
    ON [t].[DeviceNumber] = [d].[AVL_DeviceNumber]
WHERE [t].[DeviceNumber] IN ('013950003919546', '358244017672256', '013950003919876', '013227005026319', '013226001974274') 
ORDER BY
         [t].TimeStamp DESC
相关文章：