mysql去重复 - 保留一条
来源:趣味经验馆 3.25W
<link rel="stylesheet" href="https://js.how234.com/third-party/SyntaxHighlighter/shCoreDefault.css" type="text/css" /><script type="text/javascript" src="https://js.how234.com/third-party/SyntaxHighlighter/shCore.js"></script><script type="text/javascript"> SyntaxHighlighter.all(); </script>mysql去重复 保留一条怎么操作呢?一起来看下吧:
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
SELECT *FROM peopleWHERE peopleId IN ( SELECT peopleId FROM people GROUP BY peopleId HAVING count(peopleId) > 1 )
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
DELETEFROM peopleWHERE peopleName IN ( SELECT peopleName FROM people GROUP BY peopleName HAVING count(peopleName) > 1 )AND peopleId NOT IN ( SELECT min(peopleId) FROM people GROUP BY peopleName HAVING count(peopleName) > 1)
3、查找表中多余的重复记录(多个字段)
SELECT *FROM vitae aWHERE (a.peopleId, a.seq) IN ( SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1 )
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
DELETEFROM vitae aWHERE (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 aWHERE (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)
6、消除一个字段的左边的第一位:
UPDATE tableNameSET [ Title ]= RIGHT ([ Title ],(len([ Title ]) - 1))WHERE Title LIKE '村%'
7、消除一个字段的右边的第一位:
UPDATE tableNameSET [ Title ]= LEFT ([ Title ],(len([ Title ]) - 1))WHERE Title LIKE '%村'
8、假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
UPDATE vitaeSET ispass =- 1WHERE peopleId IN ( SELECT peopleId FROM vitae GROUP BY peopleId