mysql重复记录取最后一条记录方法

在应用中一个表中出现大量重复记录是常事,但有的时间我们希望过滤重复数据并取重复记录的一条记录,下面我来给大家介绍一个取重复记录其中一条的方法.

如下表,代码如下:

1. CREATETABLE`t1`(
2. `userid`INT(11)DEFAULTNULL,
3. `atime`datetimeDEFAULTNULL,
4. KEY`idx_userid`(`userid`)
5. )ENGINE=InnoDBDEFAULTCHARSET=utf8；

数据如下,代码如下:

1. MySQL>SELECT*FROMt1;
2. +--------+---------------------+
3. |userid|atime|
4. +--------+---------------------+
5. |1|2013-08-1211:05:25|
6. |2|2013-08-1211:05:29|
7. |3|2013-08-1211:05:32|
8. |5|2013-08-1211:05:34|
9. |1|2013-08-1211:05:40|
10. |2|2013-08-1211:05:43|
11. |3|2013-08-1211:05:48|
12. |5|2013-08-1211:06:03|
13. +--------+---------------------+
14. 8ROWSINSET(0.00sec)

其中userid不唯一,要求取表中每个userid对应的时间离现在最近的一条记录,初看到一个这条件一般都会想到借用临时表及添加主建借助于join操作之类的.

给一个简方法,代码如下:

1. MySQL>SELECTuserid,substring_index(group_concat(atimeORDERBYatimeDESC),",",1)ASatimeFROMt1GROUPBYuserid;
2. +--------+---------------------+
3. |userid|atime|
4. +--------+---------------------+
5. |1|2013-08-1211:05:40|
6. |2|2013-08-1211:05:43|
7. |3|2013-08-1211:05:48|
8. |5|2013-08-1211:06:03|--phpfensi.com
9. +--------+---------------------+
10. 4ROWSINSET(0.03sec)

查询及删除重复记录,删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录,代码如下:

1. deletefrompeople
2. wherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)
3. androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(peopleId)>1)

查找表中多余的重复记录(多个字段),代码如下:

1. select*fromvitaea
2. where(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)

删除表中多余的重复记录(多个字段),只留有rowid最小的记录,代码如下:

1. deletefromvitaea
2. where(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)
3. androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)

查找表中多余的重复记录(多个字段),不包含rowid最小的记录,代码如下:

1. select*fromvitaea
2. where(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)
3. androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)