SELECT * FROM stu A JOIN (SELECT MAX(id) id,name FROM stu GROUP BY name HAVING COUNT(name)>1)B ON A.id=B.id
我没测试,我以前写SQL SERVER就这样写的~!
搞错,应该是这样 SQL> SELECT * FROM stu A 2 WHERE EXISTS(SELECT name FROM stu 3 WHERE A.name=name GROUP BY name 4 HAVING COUNT(name)>1); ID NAME GRADE ---------- ---------- ---------- 100 lili 90 101 wang 89 103 lili 80 105 wang 86SQL>
(SELECT MAX(id) id,name FROM stu GROUP BY name HAVING COUNT(name)>1)B
ON A.id=B.id
SQL> SELECT * FROM stu A
2 WHERE EXISTS(SELECT name FROM stu
3 WHERE A.name=name GROUP BY name
4 HAVING COUNT(name)>1); ID NAME GRADE
---------- ---------- ----------
100 lili 90
101 wang 89
103 lili 80
105 wang 86SQL>