数据里面有多个重复IP的数据,我们需要获取最新状态的那条记录
distinct去重只能获取到唯一的IP,但更多信息就要自己再查了
id | host | ip | disk | createTime |
---|---|---|---|---|
1 | A1 | 192.168.0.1 | 100G | 2021-08-16 |
2 | A2 | 192.168.0.2 | 100G | 2021-08-16 |
3 | A1 | 192.168.0.1 | 99G | 2021-08-17 |
4 | A2 | 192.168.0.2 | 99G | 2021-08-17 |
SELECT * FROM node_info
WHERE id IN (SELECT MAX(id) FROM node_info GROUP BY ip );
当id是自增型数据时,处理比较简单,取出每个IP最大id的记录即可
但是当id是guid等其他值时就要用复杂一些的方法了
SELECT a1.* FROM node_info a1
JOIN (SELECT ip, MAX(create_time) AS `create_time` FROM node_info GROUP BY ip ) a2
ON a1.ip = a2.ip AND a1.create_time = a2.create_time