mysql - 获取每个truckId的GPS数据(纬度，经度，地址)以及最新的时间日期

嘿，伙计们，我有下表：
main（rowid、posdatetime、truckid、lat、lon、address）
行ID:PK
posdatetime：坐标和地址的日期和时间
卡车司机
纬度，经度
地址：地址字符串
行不是按日期时间顺序馈送的。
要获取每辆卡车的最新位置，我使用此查询点击数据库FOR EACHtruck：
选择*from main where truckid=xxxx order by posdatetime desc limit 1
它得到了我想要的东西，但我觉得它效率低下，我试过使用MAX()但它得到的数据行不是MAX()值的数据行。
有什么方法可以做到：
选择*from main where rowid=max（posdatetime）.rowid group by truckid
有类似的东西吗？

最佳答案

一个解决方案，将很快总是给一辆卡车最后一个指定的位置。
另一种解决方案是将posdatetime和mainid这两列添加到truck表并更改触发器中的逻辑。
该解决方案假设您有一个支持触发器的mysql版本。

-- drop table if exists last_position_for_truck ;

CREATE TABLE if not exists `last_position_for_truck` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `truckId` int(11) unsigned NOT NULL,
  `mainId` int(11) unsigned NOT NULL,
  `posDateTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `truckId` (`truckId`)
) ENGINE=myisam DEFAULT CHARSET=latin1;

-- make sure there is a one to one relationship with trucks and last_position_for_truck
-- can be run multiple times
-- must be run if a new truck is added if nothing else is done to maintain the one to one relationship
insert into `last_position_for_truck` ( truckId, mainId, posDateTime )
select truckId, mainId, posDateTime from (
    select truck.id truckId, 0 mainId,  DATE_SUB(NOW(), INTERVAL 1 YEAR) `posDateTime`
    , last_position_for_truck.id last_position_for_truck_id
    from truck
    left join last_position_for_truck
    on last_position_for_truck.truckId = truck.id
) last_position_for_truck
where last_position_for_truck_id is null ;

-- DROP TRIGGER if exists main_insert_trigger ;

delimiter $$
CREATE TRIGGER main_insert_trigger AFTER insert ON main
  FOR EACH ROW BEGIN
    update last_position_for_truck
    set posDateTime = new.posDateTime
    , mainId = new.id
    where truckId = NEW.truckId and posDateTime < NEW.posDateTime ;
  END$$
delimiter ;

-- assume there is a truck id of 1 and 2, -35.8739731, 152.22774 is somewhere in Asutralia
insert into main( truckId, posDateTime, lat, lon, address ) values ( 2, now(), -35.8739731, 152.22774, 'Somewhere in Australia' ) ;
insert into main( truckId, posDateTime, lat, lon, address ) values ( 1, now(), -35.8739731, 152.22774, 'Somewhere in Australia' ) ;

-- see the results
select last_position_for_truck.truckId
, last_position_for_truck.mainId
, main.lat
, main.lon
, main.`posDateTime`
from last_position_for_truck
left join main
on main.id = last_position_for_truck.mainId
where last_position_for_truck.id in (1,2) ;

-- sample results
1   14  -35.874 152.228 2013-01-15 11:00:18
2   13  -35.874 152.228 2013-01-15 10:59:33

关于mysql - 获取每个truckId的GPS数据(纬度，经度，地址)以及最新的时间日期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14314431/