Mysql 获取设备表中每台设备的最近的一条信息

SELECT a.id,b.addtime,a.is_maintain,a.sig_name FROM aa a LEFT JOIN 
 (SELECT d_id, MAX(addtime) addtime FROM bb GROUP BY d_id) b 
 ON a.id = b.d_id ORDER BY a.id

每个设备每五分钟传送一条数据  求每个设备最新的数据

tp5.1的写法：这个sub必须在第一行或者第二行  关联设备表  sub的设备表id = 设备表的id  然后关联数据表  这个id = 数据表的id

//device_id 是设备id
$sub = DustCollection::field('max(id) id,device_id')
                ->group('device_id')
                ->buildSql(true);

            $list =DustDevice::alias("dd")
                ->leftJoin("{$sub} dc","dc.device_id = dd.id")
                ->leftJoin("dust_collection du","du.id = dc.id")
                ->field("dd.id as did,du.pm_two_five,du.pm_ten,s.status as s_status,dd.device_name,dd.status as d_status,dd.device_lgt,dd.device_lat")
                ->select();

查询出来是这样的：

SELECT dd.id as did,s.status as s_status,`dd`.`device_name`,dd.status as d_status,`dd`.`device_lgt`,`dd`.`device_lat`,`dc`.`pm_two_five`,`dc`.`pm_ten` FROM `dust_device` `dd` LEFT JOIN `dust_collection` `du` ON `du`.`device_id`=`dd`.`id` LEFT JOIN ( SELECT id,max(collection_time) collection_time,pm_two_five,pm_ten FROM `dust_collection` WHERE `dust_collection`.`delete_time` IS NULL GROUP BY `device_id` ) dc ON `dc`.`id`=`du`.`id`  AND `dd`.`delete_time` IS NULL