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) |