一个最简单的方法,distinct去重复知道吧~用语句把所有去掉重复的记录查出来放进表A中,然后把表A的名字改成原来的,原来的删掉
假设表名为:ta
其中 id 为主键
其中:key_name 就是你需要不发生重复的字段
删除重复项
------------------------------------------------------------------------------------
delete ta where id not in
( select max(id) from group by key_name )
------------------------------------------------------------------------------------
要显示不重复项的数据
select * from ta where id in
( select max(id) from group by key_name )
------------------------------------------------------------------------------------
希望能给你点帮助。
happy day
譬如删除id相同的记录
delete from 表名 a
where (a.id) in (select * from 表名 group by id having count(*) > 1)
and rowid not in (select min(rowid) from 表名 group by id having count(*)>1)
删除sybase表中的重复行方法如下:
1)select distinct a ,b,c,d … into tempdb..tmp_tablename from tablename
然后清空原表,把临时表中的数据插入正式表中!
2)创建相同的表并建一个唯一性索引:create unique index(列名1,列名2…) on tmp_tab with ignore_dup_key
insert into new_tab select * from your_dup_tab
新表中将自动删出重复记录。
DELETE FROM YourTableName
WHERE ROWID IN (
SELECT rid
FROM (SELECT ROWID rid,
ROW_NUMBER () OVER (PARTITION BY YourColumn ORDER BY ROWID) rn
FROM YourTableName)
WHERE rn > 1)