学
习
sql
有一段
时间
了,
发现
在我建了一个用来
测试
的表(没有建索引)中出
现
了
许
多的重
复记录
。后来
总结
了一些
删
除重
复记录
的方法,在
Oracle
中,可以通
过
唯一
rowid
实现删
除重
复记录
;
还
可以建
临时
表来
实现
...
这
个只提到其中的几
种简单实
用的方法,希望可以和大家分享(以表
employee
为
例)。
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ------------------
emp_id NUMBER(10)
emp_name VARCHAR2(20)
salary NUMBER(10,2)
可以通 过 下面的 语 句 查询 重 复 的 记录 :
SQL> select * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
1 sunshine 10000
2 semon 20000
2 semon 20000
3 xyz 30000
2 semon 20000
SQL> select distinct * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
3 xyz 30000
SQL> select * from employee group by emp_id,emp_name,salary having count (*)>1
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
SQL> select * from employee e1
where rowid in (select max(rowid) from employe e2
where e1.emp_id=e2.emp_id and
e1.emp_name=e2.emp_name and e1.salary=e2.salary);
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
3 xyz 30000
2 semon 20000
2. 删 除的几 种 方法:
( 1 )通 过 建立 临时 表来 实现
SQL>create table temp_emp as (select distinct * from employee)
SQL> truncate table employee; ( 清空 employee 表的数据)
SQL> insert into employee select * from temp_emp; ( 再将 临时 表里的内容插回来)
( 2 )通 过 唯一 rowid 实现删 除重 复记录 . 在 Oracle 中, 每 一条 记录 都有一个 rowid , rowid 在整个数据 库 中是唯一的, rowid 确定了 每 条 记录 是在 Oracle 中的哪一个数据文件、 块 、行上。 在重 复 的 记录 中,可能所有列的内容都相同,但 rowid 不会相同,所以只要确定出重 复记录 中那些具有最大或最小 rowid 的就可以了,其余全部 删 除。
SQL>delete from employee e2 where rowid not in (
select max(e1.rowid) from employee e1 where
e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);-- 这 里用 min(rowid) 也可以。
SQL>delete from employee e2 where rowid <(
select max(e1.rowid) from employee e1 where
e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and
e1.salary=e2.salary);
( 3 )也是通 过 rowid ,但效率更高。
SQL>delete from employee where rowid not in (
select max(t1.rowid) from employee t1 group by
t1.emp_id,t1.emp_name,t1.salary);-- 这 里用 min(rowid) 也可以。
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
3 xyz 30000
2 semon 20000
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ------------------
emp_id NUMBER(10)
emp_name VARCHAR2(20)
salary NUMBER(10,2)
可以通 过 下面的 语 句 查询 重 复 的 记录 :
SQL> select * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
1 sunshine 10000
2 semon 20000
2 semon 20000
3 xyz 30000
2 semon 20000
SQL> select distinct * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
3 xyz 30000
SQL> select * from employee group by emp_id,emp_name,salary having count (*)>1
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
SQL> select * from employee e1
where rowid in (select max(rowid) from employe e2
where e1.emp_id=e2.emp_id and
CREATE PROCEDURE [K_FIND_CF] -- 此存 储过 程用于 查 找表中的重 值 于 2005-07-18 完成
AS
if exists (select * from sysobjects where id = object_id(N'[dbo].[T_find]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_find]
CREATE TABLE [dbo].[T_find] (
[cfz] [char](16)
)
select * from GJ_JBXX order by gjbh ------ 修改 成你要找的表名和字段名
declare sum1 scroll cursor
for
select count(*) from GJ_JBXX ------ 修改 成你要找的表名
open sum1
declare @zshu int
fetch first from sum1 into @zshu
close sum1
deallocate sum1
declare find1 scroll cursor
for
select gjbh from GJ_JBXX ------ 修改 成你要找的表名和字段名
open find1
declare find2 scroll cursor
for --update -- 指定游 标结 果集可以被修改
select gjbh from GJ_JBXX ------- 修改 成你要找的表名和字段名
open find2
declare @yb1 char(16)
declare @yb2 char(16)
fetch first from find2 into @yb2
while(@@fetch_status<>-1) --if @@fetch_status ( 游 标 当前所指的行)在最后一行 时 , @@fetch_status 的 值为 -1 ,其它情况都 为 0
begin
fetch next from find1 into @yb1
fetch next from find2 into @yb2
if (@yb1=@yb2) and (@@fetch_status<>-1) -- 必 须 有 这 一句,否 则 最后一条 记录 将 认为 是重 复 的
insert into dbo.T_find(cfz) values(@yb2)
end
close find1
deallocate find1
close find2
deallocate find2
select * from T_find
GO
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ------------------
emp_id NUMBER(10)
emp_name VARCHAR2(20)
salary NUMBER(10,2)
可以通 过 下面的 语 句 查询 重 复 的 记录 :
SQL> select * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
1 sunshine 10000
2 semon 20000
2 semon 20000
3 xyz 30000
2 semon 20000
SQL> select distinct * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
3 xyz 30000
SQL> select * from employee group by emp_id,emp_name,salary having count (*)>1
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
SQL> select * from employee e1
where rowid in (select max(rowid) from employe e2
where e1.emp_id=e2.emp_id and
e1.emp_name=e2.emp_name and e1.salary=e2.salary);
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
3 xyz 30000
2 semon 20000
2. 删 除的几 种 方法:
( 1 )通 过 建立 临时 表来 实现
SQL>create table temp_emp as (select distinct * from employee)
SQL> truncate table employee; ( 清空 employee 表的数据)
SQL> insert into employee select * from temp_emp; ( 再将 临时 表里的内容插回来)
( 2 )通 过 唯一 rowid 实现删 除重 复记录 . 在 Oracle 中, 每 一条 记录 都有一个 rowid , rowid 在整个数据 库 中是唯一的, rowid 确定了 每 条 记录 是在 Oracle 中的哪一个数据文件、 块 、行上。 在重 复 的 记录 中,可能所有列的内容都相同,但 rowid 不会相同,所以只要确定出重 复记录 中那些具有最大或最小 rowid 的就可以了,其余全部 删 除。
SQL>delete from employee e2 where rowid not in (
select max(e1.rowid) from employee e1 where
e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);-- 这 里用 min(rowid) 也可以。
SQL>delete from employee e2 where rowid <(
select max(e1.rowid) from employee e1 where
e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and
e1.salary=e2.salary);
( 3 )也是通 过 rowid ,但效率更高。
SQL>delete from employee where rowid not in (
select max(t1.rowid) from employee t1 group by
t1.emp_id,t1.emp_name,t1.salary);-- 这 里用 min(rowid) 也可以。
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
3 xyz 30000
2 semon 20000
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ------------------
emp_id NUMBER(10)
emp_name VARCHAR2(20)
salary NUMBER(10,2)
可以通 过 下面的 语 句 查询 重 复 的 记录 :
SQL> select * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
1 sunshine 10000
2 semon 20000
2 semon 20000
3 xyz 30000
2 semon 20000
SQL> select distinct * from employee;
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
3 xyz 30000
SQL> select * from employee group by emp_id,emp_name,salary having count (*)>1
EMP_ID EMP_NAME SALARY
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
SQL> select * from employee e1
where rowid in (select max(rowid) from employe e2
where e1.emp_id=e2.emp_id and
CREATE PROCEDURE [K_FIND_CF] -- 此存 储过 程用于 查 找表中的重 值 于 2005-07-18 完成
AS
if exists (select * from sysobjects where id = object_id(N'[dbo].[T_find]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_find]
CREATE TABLE [dbo].[T_find] (
[cfz] [char](16)
)
select * from GJ_JBXX order by gjbh ------ 修改 成你要找的表名和字段名
declare sum1 scroll cursor
for
select count(*) from GJ_JBXX ------ 修改 成你要找的表名
open sum1
declare @zshu int
fetch first from sum1 into @zshu
close sum1
deallocate sum1
declare find1 scroll cursor
for
select gjbh from GJ_JBXX ------ 修改 成你要找的表名和字段名
open find1
declare find2 scroll cursor
for --update -- 指定游 标结 果集可以被修改
select gjbh from GJ_JBXX ------- 修改 成你要找的表名和字段名
open find2
declare @yb1 char(16)
declare @yb2 char(16)
fetch first from find2 into @yb2
while(@@fetch_status<>-1) --if @@fetch_status ( 游 标 当前所指的行)在最后一行 时 , @@fetch_status 的 值为 -1 ,其它情况都 为 0
begin
fetch next from find1 into @yb1
fetch next from find2 into @yb2
if (@yb1=@yb2) and (@@fetch_status<>-1) -- 必 须 有 这 一句,否 则 最后一条 记录 将 认为 是重 复 的
insert into dbo.T_find(cfz) values(@yb2)
end
close find1
deallocate find1
close find2
deallocate find2
select * from T_find
GO