Create Table test (aaa Varchar2(50),bbb Varchar2(50));Insert Into test Values('fshsa','a');
Insert Into test Values('gfshf','a');
Insert Into test Values('ggaes','a');
Insert Into test Values('bdfss','b');
Insert Into test Values('refhd','b');
Insert Into test Values('gdree','c');
Insert Into test Values('jhgjy','c');
Insert Into test Values('njgft','d');
Insert Into test Values('bfhgt','d');
Insert Into test Values('vfhut','e');
Insert Into test Values('vfhrw','e');
Insert Into test Values('gfesq','e');
Insert Into test Values('gfesq','f');
Insert Into test Values('fhert','g');
Insert Into test Values('gdefe','g');
SQL> select * from test;AAA BBB
---------- ----------
fshsa a
gfshf a
ggaes a
bdfss b
refhd b
gdree c
jhgjy c
njgft d
bfhgt d
vfhut e
vfhrw e
gfesq e
gfesq f
fhert g
gdefe g-----------------------------------
要求删除BBB列中相同内容的行,只保留一个,结果如下所示:SQL> select * from test;AAA BBB
---------- ----------
ggaes a
bdfss b
gdree c
bfhgt d
vfhut e
gfesq f
gdefe g-------------------------------------
这个删除SQL要怎么写呢?哪位高手指点下?
Insert Into test Values('gfshf','a');
Insert Into test Values('ggaes','a');
Insert Into test Values('bdfss','b');
Insert Into test Values('refhd','b');
Insert Into test Values('gdree','c');
Insert Into test Values('jhgjy','c');
Insert Into test Values('njgft','d');
Insert Into test Values('bfhgt','d');
Insert Into test Values('vfhut','e');
Insert Into test Values('vfhrw','e');
Insert Into test Values('gfesq','e');
Insert Into test Values('gfesq','f');
Insert Into test Values('fhert','g');
Insert Into test Values('gdefe','g');
SQL> select * from test;AAA BBB
---------- ----------
fshsa a
gfshf a
ggaes a
bdfss b
refhd b
gdree c
jhgjy c
njgft d
bfhgt d
vfhut e
vfhrw e
gfesq e
gfesq f
fhert g
gdefe g-----------------------------------
要求删除BBB列中相同内容的行,只保留一个,结果如下所示:SQL> select * from test;AAA BBB
---------- ----------
ggaes a
bdfss b
gdree c
bfhgt d
vfhut e
gfesq f
gdefe g-------------------------------------
这个删除SQL要怎么写呢?哪位高手指点下?
delete from test t where (bbb,aaa) not in (select bbb,max(aaa) from test group by bbb)
where rowid not in
(select min(rowid)
from test t
group by t.bbb)