注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

两种新去重方法和显示哪些数据重复的方法  

2014-09-02 11:24:41|  分类: sql与pl/sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

显示去重后的数据:
1. select distinct * from t;

2. select * from t
   union
   select * from t where rownum<1;


显示哪些数据有重复(相同重复行只显示一行):
select count(1) 重复数,                  OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
                  from t
                 group by OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
                having count(1) > 1;

 

显示哪些数据有重复(如有三个及以上重复行则显示出两行,可直接删除查询出来的这些重复数据):
select *
  from t
 where rowid in (select rid
                   from (select rowid rid,
                                row_number() over(partition by OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME ORDER by CREATED desc) rn
                           from t)
                  where rn > 1);

 

 

 


实例:
SQL> create table t as select * from dba_objects;
Table created
 
SQL> insert into t select * from t where rownum<=100;
100 rows inserted
 
SQL> commit;
 
Commit complete

行总数:
SQL> select count(1) from t;
 
  COUNT(1)
----------
     84079

去重后行数:
SQL> select count(1) from (select distinct * from t);
 
  COUNT(1)
----------
     83979
 
去重后行数:
SQL> select count(1) from
  2  (select * from t
  3   union
  4   select * from t where rownum<1);
 
  COUNT(1)
----------
     83979
 

重复值是:
SQL> select count(1) from
  2  (select count(1) 重复数,
  3                        OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  4                    from t
  5                   group by OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  6                  having count(1) > 1);
 
  COUNT(1)
----------
       100

  评论这张
 
阅读(176)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018