如何将多个over/order by查询调用转换为一个？

d4so4syb 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(248)

我有以下现有的sql查询，它按特定车辆和结束时间的时间降序返回5个以前的gps位置记录：

SELECT TOP 5 - ROW_NUMBER() OVER( ORDER BY [Time] DESC) AS step ,[Time]
                                      ,[VehiculeID]
                                      ,[VehiculeUserID]
                                      ,[Latitude]
                                      ,[Longitude]
                                      ,[Speed]
                                      ,[Heading]
                                      ,[Altitude]
                                      ,[SatelliteCount]
                                      ,[HDOP]
                                      ,[VDOP]
                                      ,[EngineOn]
                                      ,[PrivacyOn]
                                      ,[Mileage]
                                      ,[DbInsertTime]
                                      ,[ReceivedTime]
                                  FROM [dbo].[GpsPosition]
                                  WHERE [Time] < @Time
                                    AND VehicleID = @VehicleID
                                    ORDER BY [Time] DESC

现在，我正在为每个vehicleid/时间元组运行多次查询：
查询一：

SELECT TOP 5 - ROW_NUMBER() OVER( ORDER BY [Time] DESC) AS step , [Time]
, [VehicleID]
, [VehiculeUserID]
, [Latitude]
, [Longitude]
, [Speed]
, [Heading]
, [Altitude]
, [SatelliteCount]
, [HDOP]
, [VDOP]
, [EngineOn]
, [PrivacyOn]
, [Mileage]
, [DbInsertTime]
, [ReceivedTime]
FROM [dbo].[GpsPosition]
WHERE [Time] < '2020-07-23 12:09:44.000'
AND VehiculeID = 359586014989545
ORDER BY [Time] DESC

...
查询n:

SELECT TOP 5 - ROW_NUMBER() OVER( ORDER BY [Time] DESC) AS step , [Time]
, [VehicleID]
, [VehiculeUserID]
, [Latitude]
, [Longitude]
, [Speed]
, [Heading]
, [Altitude]
, [SatelliteCount]
, [HDOP]
, [VDOP]
, [EngineOn]
, [PrivacyOn]
, [Mileage]
, [DbInsertTime]
, [ReceivedTime]
FROM [dbo].[GpsPosition]
WHERE [Time] < '2020-07-23 12:09:00.000'
AND VehiculeID = 12345678
ORDER BY [Time] DESC

元组值总是唯一的。
我敢肯定，我可以做得更好，只有一个调用可能是一个临时表持有元组创建显式刚才？
你怎么认为？有没有更好的方法来帮助我的一些目前未知的sql宝石呢？我已经知道cte了。

sql sql-server

来源：https://stackoverflow.com/questions/63073498/how-to-transform-many-over-order-by-query-calls-into-only-one

2条答案

按热度按时间

wlzqhblo1#

使用 partition by 并过滤 step :

SELECT p.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY VehiculeID ORDER BY [Time] DESC) AS step,
             p.*
      FROM [dbo].[GpsPosition] p
      WHERE [Time] < @Time
     ) p
WHERE step <= 5
ORDER BY VehiculeID, [Time] DESC;

我没有看到一个实用程序有一个负值 step . 但如果这是你想要的：

SELECT p.*
FROM (SELECT - ROW_NUMBER() OVER (PARTITION BY VehiculeID ORDER BY [Time] DESC) AS step,
             p.*
      FROM [dbo].[GpsPosition] p
      WHERE [Time] < @Time
     ) p
WHERE (- step) <= 5
ORDER BY VehiculeID, [Time] DESC;

编辑：
如果您只需要另一个表中车辆/时间对的子查询，那么使用 JOIN 在子查询中：

SELECT p.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY VehiculeID ORDER BY [Time] DESC) AS step,
             p.*
      FROM [dbo].[GpsPosition] p JOIN
           undescribed_table_in_question utq
           ON p.VehiculeID = utq.VehiculeID AND
              p.[Time] < utq.[Time]
     ) p
WHERE step <= 5
ORDER BY VehiculeID, [Time] DESC;

赞(0）回复(0）举报 2021-07-24

62lalag42#

您可以将它们存储在表变量中并进行相应的处理。

DECLARE @VehicleTime table(VehiculeID DECIMAL(18,2), time datetime2)

INSERT INTO @vehicleTime 
VALUES (359586014989545,'2020-07-23 12:09:44.000'),
(12345678, '2020-07-23 12:09:00.000')

SELECT -step,
[Time]
, [VehicleID]
, [VehiculeUserID]
, [Latitude]
, [Longitude]
, [Speed]
, [Heading]
, [Altitude]
, [SatelliteCount]
, [HDOP]
, [VDOP]
, [EngineOn]
, [PrivacyOn]
, [Mileage]
, [DbInsertTime]
, [ReceivedTime]
FROM
(
ROW_NUMBER() OVER(PARTITION BY VehiculeID ORDER BY [Time] DESC) AS step , 
[Time]
, [VehicleID]
, [VehiculeUserID]
, [Latitude]
, [Longitude]
, [Speed]
, [Heading]
, [Altitude]
, [SatelliteCount]
, [HDOP]
, [VDOP]
, [EngineOn]
, [PrivacyOn]
, [Mileage]
, [DbInsertTime]
, [ReceivedTime]
FROM [dbo].[GpsPosition] as gp
INNER JOIN @vehicleTime  as vt
on gp.[Time] < vt.[Time] 
AND VehiculeID = vt.vehiculeID
) as t
where step <= 5

赞(0）回复(0）举报 2021-07-24

我来回答

如何将多个over/order by查询调用转换为一个？

2条答案

相关问题

热门标签

最新问答