查找与删除表中重复记录的步骤方法,自动编号的实现
分类:微服架构

这时候如果临时表中有重复数据,无论是主键字段businessid有重复,还是一整行有重复都会报出违反唯一主键约束错误。

--注意:rownum和rowid只有Oracle有,其它数据库是不支持的

首先,建个表:

方法:group by XX having count(*)>1,rowid,distinct,temporary table,procedure

select * from scott.dept;
--查询的结果称为'结果集'

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, --主键,每次insert时让它自动加1
  artistname  TEXT
);

1、查询表中的重复数据
a.重复一个字段

--rownum 伪列 '结果集'中产生的序列
--在下面的结果集中deptno为20的rownum为2
select rownum,deptno,dname,loc from scott.dept;

insert语句这样写:

b.重复多个字段

--在下面的结果集中deptno为20,30的rownum分别为1,2
select rownum,deptno,dname,loc from scott.dept where deptno in(20,30);
--rownum=1 1条数据
select rownum,deptno,dname,loc from scott.dept where rownum = 1;
--rownum=2 0条数据
select rownum,deptno,dname,loc from scott.dept where rownum = 2;
--rownum<3 2条数据
select rownum,deptno,dname,loc from scott.dept where rownum < 3;
--rownum>0 4条数据
select rownum,deptno,dname,loc from scott.dept where rownum > 0;
--rownum>1 正常理解是3条数据,但是结果没有
--没有第一条伪列,就没有后面的伪列,伪列是从1开始递增有顺序的
select rownum,deptno,dname,loc from scott.dept where rownum > 1;

insert into artist (artistid,artistname) values ((SELECT last_insert_rowid())+1,'a');
insert into artist (artistid,artistname) values ((SELECT last_insert_rowid())+1,'b');

c.重复一整行

/*
行的标识称为字段
列的标识称为字段名

结果是:

创建测试表:

Oracle对rownum的处理,
rownum是在得到结果集的时候产生的,用于标记结果集中结果顺序的一个字段,
这个字段被称为“伪数列”,也就是事实上不存在的一个数列。
它的特点是按“顺序标记”,而且是“逐次递增”的,
换句话说就是只有存在rownum=1的记录,才可能存在rownum=2的记录。

artistid artistname

复制代码 代码如下:
create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
insert into cfa values (1,'Albert','SCB','2011-11-11');
insert into cfa values (2,'Andy','DB','2011-11-12');
insert into cfa values (3,'Allen','HSBC','2011-11-13');

假设我们的查询条件伪rownum=2,那么在查询出的第一条记录的时候,
oracle标记此条记录rownum为1,结果发现和rownum=2的条件不符,于是结果集为空。
*/


---------------以下为重复数据----------------------------------------------
insert into cfa values (1,'Alex','ICBC','2011-11-14');
insert into cfa values (1,'Albert','CTBK','2011-11-15');
insert into cfa values (1,'Albert','SCB','2011-11-11');

--在Oracle中,利用rownum分页,mysql:limit,sql:server top
select empno,ename from scott.emp;

1 a
2 b

对于a的情况,只有businessid重复

--查询emp中第6-10条
--in结果集在6,10
select rownum,empno,ename from scott.emp where rownum in (6,10);
--between 结果集在6,7,8,9,10(oracle)
--没有1,不能查询结果
select rownum,empno,ename from scott.emp where rownum between 6 and 10;
--有1,能查到结果
select rownum,empno,ename from scott.emp where rownum between 1 and 10;

复制代码 代码如下:
select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);

--当前页面数 1 每页显示数 5
--利用子查询
--between
select * from(
select rownum as tempid,empno,ename
from scott.emp
)t1
where t1.tempid between 6 and 10;

如果是b的情况,businessid 和name同时存在重复
复制代码 代码如下:
select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);

--in
select * from(
select rownum as tempid,empno,ename
from scott.emp
)t1
where t1.tempid in (6,10);

对于c的情况,重复一整行

--练习:提取scott.dept中第3条-第4条记录
select * from(
select rownum as tempid,empno,ename
from scott.emp
)t1
where t1.tempid between 3 and 4;

参考b的方法:
复制代码 代码如下:
select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);

/*
oracle还提供了另外一个数列:rowid
rowid和rownum不同,一般说来每一行数据对应一个rowid,而且是固定而且唯一的.
在这一行数据存入数据库的时候就确定了。可以理解成java对象中的内存地址.
可以利用rowid来查询记录,而且通过rowid查询记录是查询速度最快的查询方法.
(有谁能记住18位长度的rowid字符?)
rowid只有在表发生移动(比如表空间变化,数据导入/导出以后),才会发生变化。
*/
--查询dept表的rowid
select rowid,deptno,dname,loc from scott.dept;
--查询rowid=AAAMgxAAEAAAAAQAAA的行数据
select * from scott.dept where rowid = 'AAAMgxAAEAAAAAQAAA';

2、删除表中的重复数据
a情况,删除表中多余的重复记录,重复记录是根据单个字段(businessid)来判断,只留有rowid最小的记录

--面试题

也可以只保留rowid不是最小记录,需要把代码中的min改为max这里不再赘述。

--删除重复数据
select * from tb_test;

复制代码 代码如下:
delete from cfa
where businessid in (select businessid
from cfa
group by businessid
having count(businessid) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid
having count(businessid) > 1);

create table tb_test(
name varchar(18),
age number
);
insert into tb_test(name,age)values('tom',22);
insert into tb_test(name,age)values('jack',21);
insert into tb_test(name,age)values('tom',22);
insert into tb_test(name,age)values('alice',22);
insert into tb_test(name,age)values('tom',22);
insert into tb_test(name,age)values('scott',18);
insert into tb_test(name,age)values('scott',18);

或者,使用下面更简单高效的语句

--使用DISTINCT 过滤重复的字段
SELECT distinct name from tb_test
/*
重复数据:tom 22[3] scott 18[2]
1.删除所有重复数据
2.删除重复数据,但是保留一条(保留最大的rowid或者最小)
*/
--1
DELETE FROM tb_test
--此处WHERE nam IN将会把在子查询语句中所得到的name都删除掉
WHERE name IN(
SELECT name
FROM tb_test
GROUP BY name
HAVING COUNT(name)>1
);

复制代码 代码如下:
DELETE FROM cfa t
WHERE t.ROWID >
(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);

--保留一条
--1.通过创建临时表(ddl语句操作,很快)
--注意:使用distinct这种方式只能适用于重复数据是所有列
--当字段比较多(name,age,sex,address,phone...),但是判断重复只是name和age字段,如果是sex呢?就不能使用distinct了
create table tb_tmp as select distinct name,age from tb_test;
--清空表记录
--截断
truncate table tb_test;
--将临时表中的数据插回来
insert into tb_test(name,age) select name,age from tb_tmp;
select * from tb_tmp;

b情况,删除表中多余的重复记录(多个字段),只留有rowid最小的记录

--查看tom的rowid
select rowid,name,age from tb_test where name='tom' and age=22;

复制代码 代码如下:
delete from cfa
where (businessid,customer) in (select businessid,customer
from cfa
group by businessid,customer
having count(*) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid,customer
having count(*) > 1);

--这里max使用min也可以
--把不在最大唯一rowid的按照name,age分组的数据删除掉
delete from tb_test where rowid not in(
--查询唯一rowid
--按照最大的rowid,name,age分组,这样分组后的数据都具有唯一性,不在分组内的数据都应该被删除掉
select MAX(rowid) from tb_test group by name,age
);

或者,使用下面更简单高效的语句

delete from tb_test where rowid not in
(
select MIN(rowin) from tb_test group by name,age
);

复制代码 代码如下:
DELETE FROM cfa t
WHERE t.ROWID > (SELECT MIN(X.ROWID)
FROM cfa X
WHERE X.businessid = t.businessid
and x.customer = t.customer);

c情况,这种情况就比较简单,使用临时表方法

复制代码 代码如下:
create table cfabak as select distinct * from cfa;

truncate table cfa;--如果是生产最好对该表backup

Insert into cfa select * from cfabak;

commit;

本文由10bet手机官网发布于微服架构,转载请注明出处:查找与删除表中重复记录的步骤方法,自动编号的实现

上一篇:没有了 下一篇:Delphi多线程学习,美丽人生论坛看贴工具delphi版
猜你喜欢
热门排行
精彩图文