mysql - 在 MySQL 中通过用户名检测带有 IP 的多帐户

Question

我想通过 MySQL 查询找到多账户SELECT DISTINCT(ip) AS ip, name FROM {table} ORDER BY timestamp ASC;它以时尚的方式列出了所有这些:

ip, name, timestamp

1, me, 2013-09-19 19:58:52
2, me, 2013-09-19 18:00:00
123, us, 2013-09-19 20:58:52
2, you, 2013-09-19 09:58:52

但是如何逻辑选择:

1. 找到 name = "me"的所有 IP
2. 获取 IP 与点 1 匹配的所有行。
3. 从第 2 点选择全部。但只有 1 行用于 1 个 ip-nick 对，其中它的时间戳是最新的

伪查询:“SELECT * FROM table WHERE name = "me"+ ALL OTHER ROWS THAT MATCH "me"ip WHERE 带有 IP 的这一行必须是最新的时间戳；”

返回:

1, me, 2013-09-19 19:58:52
2, you, 2013-09-19 09:58:52

如果你知道前任答案的一半。如果没有最新的时间戳怎么办我也欢迎你回答。

Answer 1

这是#2:

SELECT t1.*
FROM Table t1
JOIN (SELECT DISTINCT ip
      FROM Table
      WHERE name = "me") me_ip
ON t1.ip = me_ip.ip

这是#3:

SELECT t2.*
FROM Table t2
JOIN (SELECT t1.ip, t1.name, MAX(t1.timestamp) maxtime
      FROM Table t1
      JOIN (SELECT DISTINCT ip
            FROM Table
            WHERE name = "me") me_ip
      ON t1.ip = me_ip.ip
      GROUP BY t1.ip, t1.name) t3
ON t2.ip = t3.ip AND t2.name = t3.name AND t2.timestamp = t3.maxtime