delete from (select * from tablename where SEQNO=RECNO order by seqno desc) where rownum<2
哦,我知道了:) 要这样做: select SEQNO,RECNO from t1 where SEQNO=(select max(SEQNO) from t1 where SEQNO=RECNO) and RECNO=(select max(RECNO) from t1 where SEQNO=RECNO) -------------------------------- 结果为: SEQNO RECNO 3 3 _________________________________
To:beckhambobo(beckham) 你的那个没法通用啊? 比如有数据如下: SEQNO RECNO NAME 1 1 aa 1 2 ee 1 3 aa 2 1 ff 2 2 bb 2 3 cc 3 1 dd 3 2 gg 3 3 hh 5 5 ffdf 5 5 ffdd —— 结果为:(达不到消除SEQNO和RECNO相等并且均为各自列最大的记录的要求) SEQNO RECNO NAME 5 5 ffdf
delete from table1 where SEQNO=(select max(SEQNO) from table1 where SEQNO=RECNO);
delete from table1 where SEQNO=(select max(SEQNO) from table1 where SEQNO=RECNO) and SEQNO=RECNO;
SEQNO RECNO NAME 1 1 ff 2 2 bb 2 3 cc 3 4 dd 3 3 gg楼上的语句恐怕不行。 比如如上的数据应该是不删除任何东西。 必须强调每个都最大并且相等。delete from t1 where SEQNO=(select max(SEQNO) from t1 ) and RECNO=(select max(RECNO) from t1) and SEQNO=RECNO
SQL> select * from test;SE RE NAME -- -- -------- 1 1 aa 1 2 ee 2 1 aa 2 2 bb 2 3 ccSQL> delete from test 2 where seqno=(select max(seqno) from test) 3 and recno=(select max(recno) from test) 4 and seqno=recno;已删除0行。SQL> insert into test values('3','3','hh');已创建 1 行。SQL> select * from test;SE RE NAME -- -- -------- 1 1 aa 1 2 ee 2 1 aa 2 2 bb 2 3 cc 3 3 hh已选择6行。SQL> delete from test 2 where seqno=(select max(seqno) from test) 3 and recno=(select max(recno) from test) 4 and seqno=recno;已删除 1 行。SQL> select * from test;SE RE NAME -- -- -------- 1 1 aa 1 2 ee 2 1 aa 2 2 bb 2 3 ccSQL>
delete <表名> where SEQNO=(select max(SEQNO) from <表名> where SEQNO=SEQNO);
不行啊
这样就删除了
SEQNO RECNO
3 1
3 2
3 3
三条记录
要这样做:
select SEQNO,RECNO from t1
where SEQNO=(select max(SEQNO) from t1 where SEQNO=RECNO)
and RECNO=(select max(RECNO) from t1 where SEQNO=RECNO)
--------------------------------
结果为:
SEQNO RECNO
3 3
_________________________________
你的那个没法通用啊?
比如有数据如下:
SEQNO RECNO NAME
1 1 aa
1 2 ee
1 3 aa
2 1 ff
2 2 bb
2 3 cc
3 1 dd
3 2 gg
3 3 hh
5 5 ffdf
5 5 ffdd
——
结果为:(达不到消除SEQNO和RECNO相等并且均为各自列最大的记录的要求)
SEQNO RECNO NAME
5 5 ffdf
and SEQNO=RECNO;
1 1 ff
2 2 bb
2 3 cc
3 4 dd
3 3 gg楼上的语句恐怕不行。
比如如上的数据应该是不删除任何东西。
必须强调每个都最大并且相等。delete from t1
where SEQNO=(select max(SEQNO) from t1 )
and RECNO=(select max(RECNO) from t1)
and SEQNO=RECNO
-- -- --------
1 1 aa
1 2 ee
2 1 aa
2 2 bb
2 3 ccSQL> delete from test
2 where seqno=(select max(seqno) from test)
3 and recno=(select max(recno) from test)
4 and seqno=recno;已删除0行。SQL> insert into test values('3','3','hh');已创建 1 行。SQL> select * from test;SE RE NAME
-- -- --------
1 1 aa
1 2 ee
2 1 aa
2 2 bb
2 3 cc
3 3 hh已选择6行。SQL> delete from test
2 where seqno=(select max(seqno) from test)
3 and recno=(select max(recno) from test)
4 and seqno=recno;已删除 1 行。SQL> select * from test;SE RE NAME
-- -- --------
1 1 aa
1 2 ee
2 1 aa
2 2 bb
2 3 ccSQL>