id number
---------------
159 88849566
...
...
...----------------
1.id number都是数字型;
2.id和number都有很多条,既不是唯一的
3.正常情况下同一个id对应同一个number
4.现在要排除不正常情况,既同一个number可能对应多于1个id,要求把这些记录找出来
5.想了一个早上了
---------------
159 88849566
...
...
...----------------
1.id number都是数字型;
2.id和number都有很多条,既不是唯一的
3.正常情况下同一个id对应同一个number
4.现在要排除不正常情况,既同一个number可能对应多于1个id,要求把这些记录找出来
5.想了一个早上了
select * from tab a where a.id in(select id from tab where id=a.id and number<>a.number)
Group by id,number Having count(*) > 1;
select * from tab
where number in (
select number
from tab
group by number
having count(*)>1
)
)
select * from tab
where number in (
select a.number from tab_a a,tab_a b
where a.number=b.number and a.id<>b.id
)
select * from tab
where number in (
select number
from tab
group by number
having count(*)>1
)
)
select * from tab
where number in (
select a.number from tab_a a,tab_a b
where a.number=b.number and a.id<>b.id
)————————————
这位的结果倒是不错 但是请看SELECT STATEMENT, GOAL = CHOOSE
RECURSIVE EXECUTION 对象名称=SYS_LE_2_0
TEMP TABLE TRANSFORMATION
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL 对象所有者=COOKDB 对象名称=TAB
SORT JOIN
VIEW 对象所有者=SYS 对象名称=VW_NSO_1
SORT UNIQUE
MERGE JOIN
SORT JOIN
VIEW 对象所有者=COOKDB
TABLE ACCESS FULL 对象所有者=SYS 对象名称=SYS_TEMP_1_0_FD9D6602
FILTER
SORT JOIN
VIEW 对象所有者=COOKDB
TABLE ACCESS FULL 对象所有者=SYS 对象名称=SYS_TEMP_1_0_FD9D6602
select distinct a.id,a.num from tab a,tab b where a.id<>b.id and a.num=b.num order by a.num,a.id
————————————————————————SELECT STATEMENT, GOAL = CHOOSE
SORT UNIQUE
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL 对象所有者=COOKDB 对象名称=TAB
FILTER
SORT JOIN
TABLE ACCESS FULL 对象所有者=COOKDB 对象名称=TAB
oracle中删除表中重复记录常见办法
办法一:
DELETE FROM TAB1 A
WHERE A.ROWID > ( SELECT MIN(B.ROWID) FROM TAB1 B WHERE A.字段=B.字段 );
其中子查询中的“WHERE A.字段=B.字段”用来写明重复条件。
——这一办法在数据记录超过10万时一般都会变得很慢。办法二:
--建立临时表,--清空原表,--插回原表,如下例:
create table temp_emp as (select distinct * from employee) ;
truncate table employee;
insert into employee select * from temp_emp;
——这一办法适用于较大的表的情况。因为是块操作,对应于大表效率会好很多。
FROM table1
WHERE Number IN
(SELECT Number
FROM table1
GROUP BY Number
HAVING COUNT(DISTINCT id)>1)
ORDER BY Number, id
select T1.id,T1.num
from table1 T1,table1 T2
where T1.Num=T2.Num
having count(T2.id)>1
group by T1.id,T1.num
select number, count(*) from table group by number having count(*) >1