mysql - 如何将 2 列分组以仅获取唯一行的最新版本？

Question

以上是输出的一部分，我试图只获取最新的 FID 和 SID 组合。即 FID: 1, SID: 1

这是我当前的SQL

 SELECT * FROM sensors, Location_polygon s_lp WHERE username = '$username'
 ORDER BY Datetime DESC;

按 FID 和 SID 分组会产生意想不到的结果，即只显示 FID = 1 和 SID = 1 的所有行。

有人知道吗？

我的预期结果:

当前带有示例数据的表结构是:'传感器'表

• [FID] => 9
• [SID] => 1
• [日期时间] => 2015-05-04 00:00:00
• [传感器 1] => 1000
• [传感器 2] => 100
• [传感器 3] => 100
• [电池] => 10

'Location_polygon' 表

• [用户名] => 朱蒂
• [FID] => 1
• [SID] => 1
• [lat1] => 31.446376
• [long1] => -83.597959
• [lat2] => 31.443103
• [long2] => -83.598051
• [lat3] => 31.442641
• [long3] => -83.594875
• [lat4] => 31.44486
• [long4] => -83.594595
• [lat5] => 31.446385
• [long5] => -83.596625
• [类型] => 沙子

Answer 1

您可以使用

获得所需的集合

select s_lp.* from Location_polygon s_lp
join(
 select fid,sid,max(time_taken) as time_taken from Location_polygon
 group by fid,sid
)x
on x.fid = s_lp.fid and x.sid=s_lp.sid and x.time_taken = s_lp.time_taken

如果您有一些额外的 where 条件，请将它们添加到第一个别名 s_lp 的末尾，例如 where s_lp.col_name = {some condition}

更新:从评论看来有 2 个表并且日期在不同的表中，因此查询将是

select s_lp.* from sensors s
join(
 select fid,sid,max(Datetime) as Datetime from sensors
 group by fid,sid
)x
on x.fid = s.fid and x.sid=s.sid and x.Datetime = s.Datetime
join Location_polygon s_lp on s_lp.fid = s.fid and s_lp.sid = s.sid
WHERE s_lp.username = '$username'