不好意思,忘了加括号了select * from tablename where id=(select max(id) from tablename where name='名字')
SQL> select * from t4; ID NAME ---------- ---------- 1 test1 2 test2 3 test3 4 test4 5 test0 6 test1 7 test2 8 test3 9 test4 10 test0 11 test1 ID NAME ---------- ---------- 12 test2 13 test3 14 test4 15 test0已选择15行。SQL> select name, id from 2 (select name ,rank () over(partition by name order by id desc) as rid , id from t4) where rid = 1;NAME ID ---------- ---------- test0 15 test1 11 test2 12 test3 13 test4 14
select * from tablename where id=(select max(id) from tablename where name=(select name from tablename group by name having(count(name))>1))name from tablename group by name having(count(name))>1应该先找出来name有重复的字段吧
select name, id from (select name ,rank () over(partition by name order by id desc) as rid , id , count(*) over(partition by name) as scount from t4) where rid = 1 and scount >= 2;
---------- ----------
1 test1
2 test2
3 test3
4 test4
5 test0
6 test1
7 test2
8 test3
9 test4
10 test0
11 test1 ID NAME
---------- ----------
12 test2
13 test3
14 test4
15 test0已选择15行。SQL> select name, id from
2 (select name ,rank () over(partition by name order by id desc) as rid , id
from t4) where rid = 1;NAME ID
---------- ----------
test0 15
test1 11
test2 12
test3 13
test4 14
(select name ,rank () over(partition by name order by id desc) as rid , id ,
count(*) over(partition by name) as scount
from t4) where rid = 1 and scount >= 2;