mysql - 如何通过两个参数选择最后一条记录？

Question

我有一个包含日志“snmp trap cisco port”的表:

mysql> select id, ip, `index`, trap_date from cisco_trap  order by id desc limit 20;

 id     ip          index  trap_date           
 61110  1582065982      6  2017-03-06 10:27:07 
 61109  1582065982      6  2017-03-06 10:21:58 
 61108   175141121     45  2017-03-05 22:40:58 
 61107   175141121     45  2017-03-05 22:40:52 
 61106   175141121     45  2017-03-05 22:39:21 
 61105   175141121     45  2017-03-05 22:20:18 
 61104   175141121     45  2017-03-05 22:17:56 
 61103   175141121     45  2017-03-05 22:17:50 
 61102  1582065982     16  2017-03-03 17:00:33 
 61101  1582065982      6  2017-03-03 17:00:36 
 61100  1582065982      6  2017-03-03 16:42:47 
 61099  1582065982     16  2017-03-03 15:29:55 
 61098  1582065982      6  2017-03-03 15:30:03 
 61097  1582065982     16  2017-03-03 15:27:28 
 61096  1582065982      6  2017-03-03 15:27:23 
 61095  1582065982      6  2017-03-03 15:26:27 
 61094  1582065982     16  2017-03-03 15:26:21 
 61093  1582065982     16  2017-03-03 15:25:58 
 61092  1582065982      6  2017-03-03 15:25:52 
 61091  1582065982      6  2017-03-03 15:23:51 

ip 是 inet_ntoa 中的 ip，index 是 cisco 端口号。如何通过每个端口(index)每个ip选择最后一条记录(log)？

Answer 1

您可以将 MAX 与 GROUP BY 结合使用。

SELECT * from cisco_trap 
JOIN (
  select ip, `index`, max(trap_date) as max_date 
  from cisco_trap
  group by ip, `index`
) t
ON t.ip = cisco_trap.ip 
AND t.`index` = cisco_trap.`index`
AND t.max_date = cisco_trap.trap_date