oracle 去除重复记录查询统计
Sql 代码
---统计各个 psam 卡号的数量(oracle&mysql)。
SELECT psamcode, count( * ) AS count
FROM cms_psamcard
GROUP BY psamcode
ORDER BY count DESC
LIMIT 20
---oracle
delete from stu a where rowid < (select max(b.rowid) from stu b where
a.no=b.no and a.name = b.name
1.rowid 只是 A 和 B 表中的一个字段,估计是整数型字段;
2.这个命令是删除 a 表中的 rowid 字段小于 b 表中与 A 表中相同 no.和 name 字段最大 rowid
值的记录
---oracle 查询表中重复记录的方法(mysql 中要使用 rowid 可创建一个整型唯一自增字
段)
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 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)