过滤多个IP的重复数据的统计场景

id	ip	name	insert_time
1	1.1.1.0	a	2025-01-01 00:01:00
2	1.1.1.1	b	2025-01-01 00:01:01
3	1.1.1.0	a	2025-01-01 00:00:01

id	ip	name	insert_time
1	1.1.1.0	a	2025-01-01 00:01:00
2	1.1.1.1	b	2025-01-01 00:01:01

CREATE TABLE user_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ip VARCHAR(15),
    name VARCHAR(50),
    insert_time DATETIME
);

INSERT INTO user_log (ip, name, insert_time) VALUES
('1.1.1.0', 'a', '2025-01-01 00:01:00'),
('1.1.1.1', 'b', '2025-01-01 00:01:01'),
('1.1.1.0', 'a', '2025-01-01 00:00:01');

select * from user_log;
id|ip     |name|insert_time        |
--|-------|----|-------------------|
 1|1.1.1.0|a   |2025-01-01 00:01:00|
 2|1.1.1.1|b   |2025-01-01 00:01:01|
 3|1.1.1.0|a   |2025-01-01 00:00:01|

SELECT id, ip, name, insert_time
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY insert_time DESC) AS rn
    FROM user_log
) t
WHERE rn = 1
ORDER BY id;

SELECT t1.*
FROM user_log t1
INNER JOIN (
    SELECT name, MAX(insert_time) AS max_time
    FROM user_log
    GROUP BY name
) t2 ON t1.name = t2.name AND t1.insert_time = t2.max_time
ORDER BY t1.id;

SELECT *
FROM user_log t1
WHERE insert_time = (
    SELECT MAX(insert_time)
    FROM user_log t2
    WHERE t1.name = t2.name
)
ORDER BY id;

SELECT t1.*
FROM user_log t1
LEFT JOIN user_log t2 
    ON t1.name = t2.name 
    AND t1.insert_time < t2.insert_time
WHERE t2.id IS NULL
ORDER BY t1.id;

id|ip     |name|insert_time        |
--|-------|----|-------------------|
 1|1.1.1.0|a   |2025-01-01 00:01:00|
 2|1.1.1.1|b   |2025-01-01 00:01:01|