查找表中重复数据和删除重复数据

查找表中的重复数据
一个id对应多个值（不管对应的值是否一样）
简单的操作：以Id字段为例
select (distinct) id from a where id in (select id from a group by id having count(id)>1);
加上distinct 只显示重复记录的一条
上述句子为什么使用子查询，优点画蛇添足。

也可以显示重复的次数，而且不用使用子查询，只查询单个字段
select 重复记录字段 form 数据表 group by houseno having count(重复记录字段)>1
select distinct id ，count(id) from a group by id having count(id)>1;

重复记录只显示一条ID值最小或最大的记录
因为要显示所有信息，就要使用到子查询

select distinct id,* from countid where id in (select min(id) from countid group by id having count(id)>1)

查询一张表中一个ID，（没有设置主键），对应多个值,输出所有的信息
select distinct id,* from countid where id in (
select id from countid group by id having count(id)>1);

只是单纯的输出一个字段id，
select distinct id from countid
group by id
having count(createdtimeid)>1；
有时同一个id，对应的值是相同的，查到这个id
如：
012 20081002
012 20081002

select distinct id from countid where id in (
select id from countid group by id having count(id)>1)
and createdtimeid in (select createdtimeid from countid group by createdtimeid having count(createdtimeid)>1)；

1.使用ROW_NUMBER删除重复数据
(delete table from table)
delete d from (
select year,quarter,results,
row_number()over(partition by year,quarter,requests order by year,quarter,requests) as row_no
from sale)as d
where d.row_no>1;

Delete d from (
select *,row_number()over (partition by
–ProgrectName,
–OverseaSupply,
NativeSupply,
SouthSupply
–NorthSupply
order by ProgrectName)as rownum
from ProgrectDetail) d
where d.rownum>1;

2.group by （group by 英文字面意思，根据（by）一定的规则，进行分组（group），先排序再分组）
（要有一个可以区别的id）
DELETE FROM ProgrectDetail
where ProgrectName in (
SELECT ProgrectName FROM ProgrectDetail
GROUP BY ProgrectName
HAVING COUNT()>1)
and ID NOT IN (SELECT MIN(ID) FROM ProgrectDetail --这一步操作保留最小id
GROUP BY ProgrectName
HAVING COUNT()>1)

3.distinct
select distinct name From student(单列相同);
select distinct *from student(所有指定列的信息都相同)

4.temp table
WITH TEST AS
(
SELECT ROW_NUMBER()
OVER(PARTITION BY Column1,Column2,Column3 ORDER BY ID )
AS NUM,* FROM TableName
)
DELETE FROM TEST
WHERE NUM != 1

5.在oracle 中 用rowid来删除重复值，rowid是Oracle的一个物理列。
deletecz where rowid not in(select min(rowid) from cz group by c1,c10,c20);

delete from bal_acctbook_info
where rowid not in (select min(rowid)
from bal_acctbook_info
where balance_id = ‘4012562452’
and op_time = ‘20171212111111’);

6. temp table (适用于少量数据)
   select distinct * into #temp1 from ProgrectDetail;
   delete from ProgrectDetail;
   insert into ProgrectDetail select *from #temp1;
   drop table #temp1;
   select *from ProgrectDetail;