mysql - MySQL的独特不过滤唯一的ID

我正在尝试使用mysql使用特定时间未出现的设备（serialno）列表显示结果（last_seen），然后仅显示具有max（last_seen）的唯一设备。 last_seen是一个初始值，它是一个数字，在看不见设备时会递增（思考分钟）。想象一个表，其中有一个带有lastnoseen“ 1”的serialno“ L123”的行，然后过一分钟，一个带有last_seen“ 2”的serialno“ L123”的一行，依此类推。使用max（last_seen），结果应显示最高编号或最后一次查看设备的时间。

到目前为止已经可以使用，但是我注意到设备序列号L123将在哪里显示两次，如何过滤结果以仅显示最高的last_seen？我已经尝试了两种使用不同场景的方案，但是它们似乎都不起作用。

作为我得到的一个例子（不工作）

email             | serialno | Last seen (min)
[email protected]   | L123     | 30
[email protected]  | K900     | 20
[email protected]  | L123     | 1 <--yes the email is different but same serialno

作为想要看到的例子

email             | serialno | Last seen (min)
[email protected]   | L123     | 30
[email protected]  | K900     | 20

方案1：在where子查询中选择不重复

SELECT
    email,
    serialno,
    max(last_seen)

FROM
    my_table

WHERE
    last_seen IN (SELECT last_seen FROM my_table WHERE last_seen > 0)
    AND
    serialno IN (SELECT distinct serialno FROM my_table)

GROUP BY
     2,1

ORDER BY
     3 DESC

方案2：分组使用后使用

SELECT
    email,
    serialno,
    max(last_seen)

FROM
    my_table

WHERE
    last_seen IN (SELECT last_seen FROM my_table WHERE last_seen > 0)

GROUP BY
    2,1

HAVING
    serialno in (SELECT distinct serialno FROM my_table)

ORDER BY
    3 DESC

最佳答案

JOIN带有子查询，用于查找每个序列号的最大last_seen值：

select t1.*
from my_table t1
join (select serialno, max(Last_seen) Last_seen
      from my_table
      group by serialno) t2
    on t1.serialno = t2.serialno and t1.Last_seen = t2.Last_seen
order by t1.Last_seen desc