MySQL-为每个设备选择最后一行的查询

Question

我的数据库中有 2 个表:

CREATE TABLE IF NOT EXISTS `location` (
  `code` int(11) NOT NULL AUTO_INCREMENT,
  `IMEI` varchar(15) NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `speed` tinyint(3) unsigned NOT NULL,
  `course` smallint(6) NOT NULL,
  `satellites` tinyint(3) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  `rt_dif` tinyint(1) NOT NULL,
  `pos_notpos` tinyint(1) NOT NULL,
  `MCC` smallint(5) unsigned NOT NULL,
  `MNC` smallint(5) unsigned NOT NULL,
  `LAC` smallint(5) unsigned NOT NULL,
  `CELL_ID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2323 ;

CREATE TABLE IF NOT EXISTS `devices` (
  `code` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `IMEI` varchar(15) NOT NULL,
  `client` int(11) NOT NULL,
  `model` varchar(50) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

我有属于不同客户端的设备，并且我想选择给定客户端的每个设备的最后一个位置行。位置消息可能按任意顺序发送，可能全部被打乱，或者最后 1000 条可能来自同一设备。

有人可以帮助我吗？

Answer 1

这是一种方法......尽管使用协作子查询速度更快。

SELECT D.*, L.*
FROM Devices D
LEFT JOIN Location L
 on D.IMEI = L.IMEI
INNER JOIN (Select max(Code) mCode, IMEI From Location group by IMEI) X  
 on D.IMEI = X.IMEI  
and D.Code = X.mCode

意图:

• 返回所有设备及其最近位置(如果存在)。
• 将设备外部连接到位置，以便我们获取所有设备
• 内连接到每个设备的最近位置的子集。此次加盟有效消除历史地点。

我的理解是，您希望由位置中的代码字段确定“最后”，记录每个设备。简单地说，您想知道系统中每个设备的最后报告位置；没有任何历史记录。

为了得到这个，我假设您只关心设备表中设备的位置记录。因此，我们从设备开始，因为我们想要所有设备，无论最后位置是否已报告。

需要给 max(code) 起别名，因为列名不是 code。