Mysql oracle删除重复记录的sql

原创于 2013-03-15 17:20:48 发布 · 175 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

ORACLE 专栏收录该内容

6 篇文章

订阅专栏

本文深入探讨了MySQL和Oracle数据库中的SQL语句优化、表操作及数据清理策略，包括使用DROP TABLE IF EXISTS、GROUP BY、HAVING子句、JOIN等关键特性进行高效数据处理。通过实例展示了如何有效地管理和维护数据库，提升数据查询性能。

MYSQL


drop TABLE if EXISTS tmp_t;
create TABLE tmp_t select min(id) id,`name`  from comp_info group by `name` having count(`name`)>1 ;
delete a.* from comp_info a,tmp_t b where b.`name`=a.`name` and a.id>b.id;
truncate table tmp_t;
drop TABLE if EXISTS tmp_t;

Oracle


delete from gqh_sch_info 
where sch_name  in (select  sch_name  from gqh_sch_info  group  by  sch_name   having  count(sch_name) > 1)
and rowid not in (select min(rowid) from  gqh_sch_info  group by sch_name  having count(sch_name)>1)