求助 <> 和 not in的问题
表数据如下:
--------------------------------------------------------------
ID NAME ADDR ROWID
1 1 test addr1 AAARGiAAEAAAAA+AAA
2 1 test addr2 AAARGiAAEAAAAA/AAA
3 1 test addr3 AAARGiAAEAAAAA/AAB
4 2 gogogo addr1 AAARGiAAEAAAAA+AAB
5 2 gogogo addr2 AAARGiAAEAAAAA/AAC
6 3 aaaaaf bbbbbb AAARGiAAEAAAAA+AAC
--------------------------------------------------------------
现在想删除重复记录。重复列为ID和NAME(测试用,无主键)
思路:获得全部重复记录,分组并取得最小rowid,删除其他rowid的行
=================================================================
SQL1:
select t.*, rowid
from emp t
where t.rowid not in (select min(rowid)
from emp e
where e.id = t.id
and e.name = t.name
group by e.id, e.name
having count(*)>1)
查询结果如下:
ID NAME ADDR ROWID
1 3 aaaaaf bbbbbb AAARGiAAEAAAAA+AAC
2 1 test addr2 AAARGiAAEAAAAA/AAA
3 1 test addr3 AAARGiAAEAAAAA/AAB
4 2 gogogo addr2 AAARGiAAEAAAAA/AAC
--------------------------------------------------------------
SQL2:
select t.*, rowid
from emp t
where t.rowid <> (select min(rowid)
from emp e
where e.id = t.id
and e.name = t.name
group by e.id, e.name
having count(*)>1)
查询结果如下:
ID NAME ADDR ROWID
1 1 test addr2 AAARGiAAEAAAAA/AAA
2 1 test addr3 AAARGiAAEAAAAA/AAB
3 2 gogogo addr2 AAARGiAAEAAAAA/AAC
--------------------------------------------------------------
问题:为何SQL1,2取得结果不同,跪求原理。望大神告知。