PL/SQL学习笔记（四）之——删除重复记录
1 例：假设员工表中有若干记录重复，请删除重复的记录（某企业面试题）
2 ------模拟建表
3 create table employee(
4 e_id varchar2(20) primary key,
5 e_name varchar2(50) not null,
6 e_sex char(2) check(e_sex in('男','女')),
7 e_age integer
8 )
9 insert into employee values('e001','Frank_Lei','男',20);
10 insert into employee values('e002','Frank_Lei','男',20);
11 insert into employee values('e003','Frank_Lei','男',20);
12 insert into employee values('e004','Frank_Lei','男',20);
13 insert into employee values('e005','美佳','女',18);
14 insert into employee values('e006','美佳','女',18);
15 insert into employee values('e007','美佳','女',18);
16 insert into employee values('e008','美佳','女',18);
17 insert into employee values('e010','美佳','女',19);
18 insert into employee values('e009','二哈','男',8);
19 select * from employee;
20 --------------方法一-------------------------
21 -----第一步：查询除 e_id 外其余字段均重复的记录
22 select e_name,e_sex,e_age from employee group by e_name,e_sex,e_age having count(*)>1
23 ----第二步：在除 e_id 外其余字段均重复的记录之中取 runid 最小的列（用于保留）
24 select min(rowid) from employee group by e_name,e_sex,e_age having count(*)>1
25 ---第三步：在一二步基础上筛选删除（删除除 e_id 之外所有字段均重复的记录，除过 runid 最小的记录）
26 delete from employee e
27 where (e.e_name,e.e_sex,e.e_age) in
28 (select e_name,e_sex,e_age from employee group by e_name,e_sex,e_age having count(*)>1)
29 and
30 rowid not in
31 (select min(rowid) from employee group by e_name,e_sex,e_age having count(*)>1);
32 rollback;
33 ------------------方法二（利用分组与not in）--------------------------
34 delete from employee where e_id not in(select max(e_id) from employee e group by e.e_name,e.e_sex,e.e_age)
用SQL语句,删除掉重复项只保留一条
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleName）来判断，只留有rowid最小的记录
delete from people
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>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)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * 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)
6.消除一个字段的左边的第一位：
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消除一个字段的右边的第一位：
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId
