Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
614 views
in Technique[技术] by (71.8m points)

sql server - How to add new data columns of old time in SQL Query?

I have this query : http://pastebin.com/JNx1hX1i

I want to add new 2 column to my query.OldLat(Lat of 5 minutes ago),OldLat(Lng of 5 minutes ago)

Result is like this

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
   .             .          .                 .                 .
   .             .          .                 .                 .
   .             .          .                 .                 .

Query:

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

After add OldLat and OldLng result to be like this :

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  
  .              .          .       .          .            .          .       
  .              .          .       .          .            .          .      
  .              .          .       .          .            .          .      
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If your OldLat and OLng is fixed then you can add the two as

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

I hope you get my point. Not an exact one but will definetly give you an idea. Below is your original query.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...