在网页上经常看到查找或者删除重复记录的高效方法,比如:
SELECT * FROM emp a
WHERE a.ROWID >
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh)但是没有找到要select过滤掉重复记录后的表记录高效语句怎么写,
1. select * from emp where rybh not in
(SELECT * FROM emp a
WHERE a.ROWID >
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh))
select * from emp a where not exists select2. SELECT *
FROM dt_a_al_gjjgrzhxx a
WHERE NOT EXISTS
(SELECT 'X'
FROM (SELECT a.rybh
FROM dt_a_al_gjjgrzhxx a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM dt_a_al_gjjgrzhxx b
WHERE b.rybh = a.rybh)) c
WHERE c.rybh = a.rybh)好像都不是很快,请高手赐教,十分感谢!
SELECT * FROM emp a
WHERE a.ROWID >
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh)但是没有找到要select过滤掉重复记录后的表记录高效语句怎么写,
1. select * from emp where rybh not in
(SELECT * FROM emp a
WHERE a.ROWID >
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh))
select * from emp a where not exists select2. SELECT *
FROM dt_a_al_gjjgrzhxx a
WHERE NOT EXISTS
(SELECT 'X'
FROM (SELECT a.rybh
FROM dt_a_al_gjjgrzhxx a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM dt_a_al_gjjgrzhxx b
WHERE b.rybh = a.rybh)) c
WHERE c.rybh = a.rybh)好像都不是很快,请高手赐教,十分感谢!
解决方案 »
- Oracel 创建表 时 有?????TTTT
- 从一个表中查询出所有的记录
- Oracle数据表中如何追加主键和索引?
- 急啊!oracle中如何查第几个星期的内容??在线等。谢谢了。
- 请教,如何显示查询全部的字段呢
- (在线等)在Windows 2000上安装Oracle9i,出现错误:ora-01041:内部错误、hostdef扩展名不存在。
- 在ORACLE里面怎么计算两个日期之间间隔的周数
- [问题] 请问一个关于Oracle中与日期相关的sql语句
- 在windows2000ADV下安装oracle8i不能出现安装界面,请问有什么方法可以解决??
- OracleOraHome81TNSListener 服务不能启动:提示“本地计算机上无法启动oracleTNSListener80服务
- oracle 全文检索问题(急)高分
- java高级技术群欢迎高手加入18891623
WHERE a.ROWID =
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh);
1. select * from emp where rowid not in
(SELECT rowid FROM emp a
WHERE a.ROWID >
(SELECT MIN(b.ROWID) FROM emp b WHERE b.rybh = a.rybh))
select * from emp a where not exists select2. SELECT *
FROM dt_a_al_gjjgrzhxx a
WHERE NOT EXISTS
(SELECT 'X'
FROM (SELECT a.rowid
FROM dt_a_al_gjjgrzhxx a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM dt_a_al_gjjgrzhxx b
WHERE b.rybh = a.rybh)) c
WHERE c.rowid = a.rowid)这两种都觉得不算快
(SELECT MIN(b.ROWID) FROM emp b group by b.rybh) b
where a.rowid=b.rowid;
如果rybh 列使用了索引的话。
ora-01446:无法从含distinct,group by等子句的视图中选择rowid
漏掉了别名select a.* from emp a,
(SELECT MIN(b.ROWID) rid FROM emp b group by b.rybh) b
where a.rowid=b.rid;自己测一下吧。
第二个要给rowid起个别名就好了:
SELECT *
FROM dt_a_al_gjjgrzhxx a,
(SELECT MIN(b.ROWID) row_id
FROM dt_a_al_gjjgrzhxx b
GROUP BY b.rybh) b
WHERE a.ROWID = b.ROW_id 第一种0.062秒,第二种3.016