mysql重復記錄取最後一條記錄方法

在應用中一個表中出現大量重復記錄是常事，但有的時間我們希望過濾重復數據並取重復記錄的一條記錄，下面我來給大家介紹一個取重復記錄其中一條的方法。

如下表：

 代碼如下 復制代碼

CREATE TABLE `t1` (
`userid` INT(11) DEFAULT NULL,
`atime` datetime DEFAULT NULL,
KEY `idx_userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

數據如下：

 代碼如下 復制代碼

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)

其中userid不唯一，要求取表中每個userid對應的時間離現在最近的一條記錄．初看到一個這條件一般都會想到借用臨時表及添加主建借助於join操作之類的．
給一個簡方法：

 代碼如下 復制代碼

MySQL> SELECT userid,substring_index(group_concat(atime ORDER BY atime DESC),",",1) AS atime FROM 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)

查詢及刪除重復記錄

刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄

 代碼如下 復制代碼 delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

3、查找表中多余的重復記錄（多個字段）

 代碼如下 復制代碼 select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄

 代碼如下 復制代碼 delete 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 having count(*)>1)

5、查找表中多余的重復記錄（多個字段），不包含rowid最小的記錄

 代碼如下 復制代碼 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 having count(*)>1)