如何在SQL查询中添加的旧时代的新的数据列？(How to add new data columns

我有这个疑问： http://pastebin.com/JNx1hX1i

我想新的2列添加到我的query.OldLat（5分钟前纬度），OldLat（5分钟前LNG）

结果是这样的

LatOfTruck   LngOfTruck SpeedOfTruck   LastTime            PlateOfTruck
36.9573      27.8099       72         11.12.2013 12:30:00      123456
34.9573      27.5053       82         11.12.2013 11:30:00      541456
38.8952      37.7855       52         11.12.2013 10:30:00      78455
   .             .          .                 .                 .
   .             .          .                 .                 .
   .             .          .                 .                 .

查询：

SELECT LatOfTruck = TruckLocation.Lat,
       LngOfTruck       = TruckLocation.Lng,
       SpeedOfTruck     = TruckLocation.Speed,
       LastTime         = CONVERT(VARCHAR(10), [TruckLocation.ReadTime], 104) +
                        ' ' + CONVERT(VARCHAR(8), [TruckLocation.ReadTime], 108),
       PlateOfTruck     = Trucks.Plate
FROM   TruckLocation
JOIN   Truck AS Trucks
       ON  Trucks.OID = TruckLocation.TruckID
WHERE  TruckLocation.OID  

       IN (SELECT MAX(TruckLocation_1.OID) AS OID
           FROM   TruckLocation AS TruckLocation_1
           JOIN   Truck
                  ON  TruckLocation_1.TruckID = Truck.OID
           GROUP BY TruckLocation_1.TruckID)
ORDER BY TruckLocation.ReadTime DESC

添加后OldLat和OldLng结果是这样的：

LatOfTruck LngOfTruck SpdOfTruck LastTime   PlateOfTruck   OldLat      OldLng
 36.9573     27.8099       72     date        123456   LatOf5mAgo  LngOf5mAgo  
 34.9573     27.8099       82     date        121456   LatOf5mAgo  LngOf5mAgo  
 38.8952     37.7855       52     date         78455   LatOf5mAgo  LngOf5mAgo  
  .              .          .       .          .            .          .       
  .              .          .       .          .            .          .      
  .              .          .       .          .            .          .      

Answer 1:

如果您OldLat和OLng是固定的，那么你可以添加两成

Select LatOfTruck, LngOfTruck,OldLat='LatOf5mAgo', OldLng='LngOf5mAgo'
From TbaleName

我希望你明白我的意思。 不是一个确切的一个，但将definetly给你一个想法。 下面是你的原始查询。

SELECT LatOfTruck=TruckLocation.Lat,LngOfTruck=TruckLocation.Lng,
SpeedOfTruck=TruckLocation.Speed,LastTime=CONVERT(VARCHAR(10), [TruckLocation.ReadTime], 104) + ' ' + CONVERT(VARCHAR(8),
[TruckLocation.ReadTime],108) 
,PlateOfTruck=Trucks.Plate,OldLat='LatOf5mAgo', OldLng='LngOf5mAgo' 
FROM   TruckLocation
JOIN Truck AS Trucks ON Trucks.OID=TruckLocation.TruckID
WHERE  TruckLocation.OID  IN ( SELECT  MAX(TruckLocation_1.OID) AS OID
                               FROM  TruckLocation AS TruckLocation_1
                               JOIN  Truck ON TruckLocation_1.TruckID = Truck.OID  
                               GROUP BY TruckLocation_1.TruckID
                             )
ORDER BY TruckLocation.ReadTime DESC

文章来源: How to add new data columns of old time in SQL Query?