假如有这样一张表news：字段：id,title,time,image,author，现在表中有1万多条记录，其中title重复的有上千条。如何才能一次性将title重复记录删除呢？
| ID |
Title |
Time |
image |
author |
| 1 |
我是高富帅 |
1234 |
pic1.jpg |
1 |
| 2 |
我是白富美 |
1233 |
pic2.jpg |
2 |
| 3 |
我是高富帅 |
1235 |
pic3.jpg |
3 |
本人使用sqlserver数据库。
现在先将所有的重复数据显示出来：
- SELECT id,title,time,image,author
- FROM news
- WHERE (title IN (SELECT title FROM news GROUP BY title HAVING (COUNT(title) > 1)))
删除多余的记录
- delete from news
- WHERE (title IN (SELECT title FROM news GROUP BY title HAVING (COUNT(title) > 1)))
- AND (id NOT IN (SELECT MIN(id) AS nid FROM news GROUP BY title HAVING (COUNT(title) > 1)))
使用min的原因是保留发布时间最早的记录，如果要保留时间最后发布的可以使用max
经验首页