例如: 方法1 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录 (三)
SQL> desc employee Name Null? Type emp_id NUMBER(10) salary NUMBER(10,2) 可以通过下面的语句查询重复的记录: SQL> select * from employee;
---------- ---------------------------------------- ---------- 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
where rowid in (select max(rowid) from employe e2 e1.emp_name=e2.emp_name and e1.salary=e2.salary);
---------- ---------------------------------------- ---------- 1 sunshine 10000 3 xyz 30000 2 semon 20000 2. 删除的几种方法:
SQL>create table temp_emp as (select distinct * from employee) SQL> truncate table employee; (清空employee表的数据) SQL> insert into employee select * from temp_emp; (再将临时表里的内容插回来)
SQL>delete from employee e2 where rowid not in ( e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以。
e1.salary=e2.salary);
SQL>delete from employee where rowid not in ( t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。 EMP_ID EMP_NAME SALARY ---------- ---------------------------------------- ---------- 1 sunshine 10000 3 xyz 30000 2 semon 20000
Name Null? Type emp_id NUMBER(10) salary NUMBER(10,2) 可以通过下面的语句查询重复的记录: SQL> select * from employee;
---------- ---------------------------------------- ---------- 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
where rowid in (select max(rowid) from employe e2 e1.emp_name=e2.emp_name and e1.salary=e2.salary);
---------- ---------------------------------------- ---------- 1 sunshine 10000 3 xyz 30000 2 semon 20000 2. 删除的几种方法:
SQL>create table temp_emp as (select distinct * from employee) SQL> truncate table employee; (清空employee表的数据) SQL> insert into employee select * from temp_emp; (再将临时表里的内容插回来)
SQL>delete from employee e2 where rowid not in ( e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以。
e1.salary=e2.salary);
SQL>delete from employee where rowid not in ( 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语句删除重复的记录的方法
最新推荐文章于 2023-03-12 21:10:00 发布
sql语句删除重复的记录的方法