萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql重復記錄取最後一條記錄方法

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)
copyright © 萬盛學電腦網 all rights reserved