mysql 尝试仅选择最新时间戳

Question

这个问题已经有答案了:

Fetch the rows which have the Max value for a column for each distinct value of another column (35 个回答)

已关闭 9 年前。

我正在尝试连接 2 个表并仅选择最新时间戳以及与该最新时间戳关联的字段值

数据表:

设备计数

id  device_name     platform    version     timestamp
1   abc             123         x           2013-12-30
2   abc             123         y           2014-01-01
3   def             345         x           2013-12-30

设备列表

id      device_name     Network     Division    Market
2657    def             a           f           d
2656    abc             a           b           c

期望的结果应该是

device_name     platform    version     network     division    market  timestamp
abc             123         y           a           b           c       2014-01-01
def             345         x           a           f           d       2013-12-30

我使用了以下查询，但没有得到正确的结果

SELECT
device_count.device_name,
device_count.platform,
device_count.version,
Max(device_count.`timestamp`),
device_list.Network,
device_list.Division,
device_list.Market
FROM
device_count
INNER JOIN device_list ON device_count.device_name = device_list.Device_Name
GROUP BY
device_count.device_name,
device_count.platform,
device_count.version,
device_list.Network,
device_list.Division,
device_list.Market

结果

device_name     platform    version     network     division    market  timestamp
abc             123         x           a           b           c       2013-12-30
abc             123         y           a           b           c       2014-01-01
def             345         x           a           f           d       2013-12-30

不应包含第一条记录。我只想要最新时间戳的版本和平台值(记录 id 2)

Answer 1

一种方法是匹配子选择:

SELECT
  device_count.device_name,
  device_count.platform,
  device_count.version,
  device_count.`timestamp`,
  device_list.Network,
  device_list.Division,
  device_list.Market
FROM
  device_count
INNER JOIN 
  device_list 
ON device_count.device_name = device_list.Device_Name
WHERE device_count.`timestamp` = (
    SELECT MAX(dc2.`timestamp`)
    FROM
      device_count AS dc2
    WHERE
      dc2.device_name =  device_count.device_name
    )