select col1 from t group by col1 having count(1)>1
count(1)是什麼意思啊,還請樓上的達人指教。
我觉得经典的查询重复记录语句 select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
--方法一 --用时: 00: 00: 29.09 select b.m_no,b.sq,b.p_roll,b.npl,b.tr_date from t_tr b, (select count(*),m_no,sq,p_roll,npl from t_tr group by m_no,sq,p_roll,npl having count(*)>1) a where b.m_no=a.m_no and b.sq=a.sq and b.p_roll=a.p_roll and b.npl=a.npl order by b.tr_date--方法二 --用时:n秒,最后只好卡叉掉 select m_no,sq,p_roll,npl,tr_date from t_tr a where a.rowid!=(select max(rowid) from t_tr b where a.m_no=b.m_no and a.sq=b.sq and a.p_roll=b.p_roll and a.npl=b.npl)--方法三 --用时:5秒, --使用oracle的分析函数select M_NO,SQ,PT,P_ROLL,P_YDS,P_KGS,tr_date,dept from TR where (M_NO,SQ,nvl(PT,'<NULL>'),p_roll) in (select M_NO,SQ,Nvl(PT,'<NULL>'),P_ROLL from (select M_NO,SQ,PT,P_ROLL,Row_Number() over (partition By M_NO,SQ,PT,P_ROLL order by M_NO,SQ,PT,P_ROLL) rn from tr order by M_NO,SQ,PT,P_ROLL) t where rn>1) and tr_date>to_date('2004-04-01','yyyy-mm-dd')小结:合理的使用oracle的分析函数可以很大的提高sql的执行效率。
col1
from
t
group by col1
having count(1)>1
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
--方法一
--用时: 00: 00: 29.09
select b.m_no,b.sq,b.p_roll,b.npl,b.tr_date from t_tr b,
(select count(*),m_no,sq,p_roll,npl
from t_tr
group by m_no,sq,p_roll,npl
having count(*)>1) a
where b.m_no=a.m_no and b.sq=a.sq and b.p_roll=a.p_roll and b.npl=a.npl
order by b.tr_date--方法二
--用时:n秒,最后只好卡叉掉
select m_no,sq,p_roll,npl,tr_date
from t_tr a
where a.rowid!=(select max(rowid)
from t_tr b
where a.m_no=b.m_no and a.sq=b.sq and a.p_roll=b.p_roll and a.npl=b.npl)--方法三
--用时:5秒,
--使用oracle的分析函数select M_NO,SQ,PT,P_ROLL,P_YDS,P_KGS,tr_date,dept from TR
where (M_NO,SQ,nvl(PT,'<NULL>'),p_roll) in
(select M_NO,SQ,Nvl(PT,'<NULL>'),P_ROLL from
(select M_NO,SQ,PT,P_ROLL,Row_Number() over (partition By M_NO,SQ,PT,P_ROLL order by M_NO,SQ,PT,P_ROLL) rn
from tr order by M_NO,SQ,PT,P_ROLL) t
where rn>1)
and tr_date>to_date('2004-04-01','yyyy-mm-dd')小结:合理的使用oracle的分析函数可以很大的提高sql的执行效率。