SQL> select * from tbl where b in(select b from tbl group by b having count(b)>1); A B ---------- ---------- 20 4 21 4SQL>
SQL> select b from tbl group by b having count(b)>1; B ---------- 4SQL>
delete from (select * from tbl where b in(select b from tbl group by b having count(b)>1));
不能直接写having count(b)>1,也可能是count(b)>2、3,这个值是外界不定的
delete from ( select * from tbl A where rowid = (select max(rowid) from tbl where A.b = b ) )
select b from tablename where a=20 intersect select b from tablename where a-21;select b from tablename where a=20 intersect select b from tablename where a=21 intersect select b from tablename where a=22;
SQL> select * from tbl where b in(select b from tbl group by b having count(b)>1); A B
---------- ----------
20 4
21 4SQL>
SQL> select b from tbl group by b having count(b)>1; B
----------
4SQL>
(select * from tbl
where b in(select b from tbl group by b having count(b)>1));
(
select * from tbl A
where rowid = (select max(rowid) from tbl where A.b = b )
)
from tablename
where a=20
intersect
select b
from tablename
where a-21;select b
from tablename
where a=20
intersect
select b
from tablename
where a=21
intersect
select b
from tablename
where a=22;