首页 / MYSQL / MySQL中删除重复数据只保留一条
MySQL中删除重复数据只保留一条
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中删除重复数据只保留一条,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2164字,纯文字阅读大概需要4分钟。
内容图文
![MySQL中删除重复数据只保留一条](/upload/InfoBanner/zyjiaocheng/485/2e31612bae6e4fc9ab13e7a2deaa2800.jpg)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
DELETEFROM
people
WHERE
peopleName IN (
SELECT
peopleName
FROM
people
GROUP BY
peopleName
HAVINGcount(peopleName) > 1
)
AND peopleId NOT IN (
SELECTmin(peopleId)
FROM
people
GROUP BY
peopleName
HAVINGcount(peopleName) > 1
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
3、查找表中多余的重复记录(多个字段)
SELECT
*
FROM
vitae a
WHERE
(a.peopleId, a.seq) IN (
SELECT
peopleId,
seq
FROM
vitae
GROUP BY
peopleId,
seq
HAVINGcount(*) > 1
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
DELETEFROM
vitae a
WHERE
(a.peopleId, a.seq) IN (
SELECT
peopleId,
seq
FROM
vitae
GROUP BY
peopleId,
seq
HAVINGcount(*) > 1
)
AND rowid NOT IN (
SELECTmin(rowid)
FROM
vitae
GROUP BY
peopleId,
seq
HAVINGcount(*) > 1
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
SELECT
*
FROM
vitae a
WHERE
(a.peopleId, a.seq) IN (
SELECT
peopleId,
seq
FROM
vitae
GROUP BY
peopleId,
seq
HAVINGcount(*) > 1
)
AND rowid NOT IN (
SELECTmin(rowid)
FROM
vitae
GROUP BY
peopleId,
seq
HAVINGcount(*) > 1
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
6.消除一个字段的左边的第一位:
UPDATE tableName
SET [ Title ]= RIGHT ([ Title ],(len([ Title ]) - 1))
WHERE
Title LIKE ‘村%‘
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
7.消除一个字段的右边的第一位:
UPDATE tableName
SET [ Title ]= LEFT ([ Title ],(len([ Title ]) - 1))
WHERE
Title LIKE ‘%村‘
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
UPDATE vitae
SET ispass =- 1WHERE
peopleId IN (
SELECT
peopleId
FROM
vitae
GROUP BY
peopleId
来源: http://blog.csdn.net/z_youarethebest/article/details/53785435来自为知笔记(Wiz)
MySQL中删除重复数据只保留一条
标签:otto pad bsp font 保留 mil tom 来源 tle
本文系统来源:http://www.cnblogs.com/jeffen/p/7016560.html
内容总结
以上是互联网集市为您收集整理的MySQL中删除重复数据只保留一条全部内容,希望文章能够帮你解决MySQL中删除重复数据只保留一条所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。