1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
SELECT * FROM people WHERE peopleId IN ( SELECT peopleId FROM people GROUP BY peopleId HAVING count(peopleId) > 1 )
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
DELETE FROM people WHERE 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 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 )
6、消除一个字段的左边的第一位:
UPDATE tableName SET [ Title ]= RIGHT ([ Title ],(len([ Title ]) - 1)) WHERE Title LIKE '村%'
7、消除一个字段的右边的第一位:
UPDATE tableName SET [ Title ]= LEFT ([ Title ],(len([ Title ]) - 1)) WHERE Title LIKE '%村'
8、假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
UPDATE vitae SET ispass =- 1 WHERE peopleId IN ( SELECT peopleId FROM vitae GROUP BY peopleId
select *
from table ###
where not exists (
select * from table ###
where # = #
and ## <##
)
在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,只有用二重循环查询来解决。
给个例子把,比如:表table_a 4条数据
id A B C D
01 ab 1a2 1b2 121
02 ab 2a3 3b3 4a1
03 ac 1a2 1b2 121
04 ac 2a4 3b2 52g
何让A字段重复取条 比
01 ab 1a2 1b2 121
03 ac 1a2 1b2 121
保留相同A值id行
select *
from table_a a
where not exists (
select 1 from table_a b
where b.A = a.A
and b.id <a.id
)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)