MySQL duplicate record to take the last record method

As shown in the following table:

The code is as follows	Copy Code
CREATE TABLE ' T1 ' ( ' UserID ' INT (one) DEFAULT NULL, ' Atime ' datetime DEFAULT NULL, KEY ' Idx_userid ' (' userid ') ) Engine=innodb DEFAULT Charset=utf8;

The data are as follows:

The code is as follows	Copy Code
Mysql> SELECT * from T1; +--------+---------------------+ | UserID | Atime | +--------+---------------------+ | 1 | 2013-08-12 11:05:25 | | 2 | 2013-08-12 11:05:29 | | 3 | 2013-08-12 11:05:32 | | 5 | 2013-08-12 11:05:34 | | 1 | 2013-08-12 11:05:40 | | 2 | 2013-08-12 11:05:43 | | 3 | 2013-08-12 11:05:48 | | 5 | 2013-08-12 11:06:03 | +--------+---------------------+ 8 ROWS in SET (0.00 sec)

The UserID is not unique, requiring that each userid in the table be taken from a recent record in the present time. At first sight this condition is generally thought to borrow temporary tables and add the main build with the help of the join operation.
Give a simple method:

The code is as follows	Copy Code
Mysql> SELECT Userid,substring_index (Group_concat (atime order by Atime DESC), ",", 1) as atime to T1 group by UserID; +--------+---------------------+ | UserID | Atime | +--------+---------------------+ | 1 | 2013-08-12 11:05:40 | | 2 | 2013-08-12 11:05:43 | | 3 | 2013-08-12 11:05:48 | | 5 | 2013-08-12 11:06:03 | +--------+---------------------+ 4 ROWS in SET (0.03 sec)

Query and delete duplicate records

Delete Redundant records in a table, and duplicate records are judged by a single field (Peopleid), leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from people where Peopleid in (select Peopleid from People GROUP by Peopleid has count (Peopleid) > 1) and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

3. Find redundant records in the table (multiple fields)

The code is as follows	Copy Code
SELECT * FROM Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

code is as follows	copy code
select * from Vitae a where (A.PEOPLEID,A.SEQ) in  (select Peopleid,seq from Vitae GROUP by Peopleid,seq having count (*) > 1 and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)