在tmp_hotel 中有一些重复的数据 , 根据 TELEPHONE 字段来判断是否重复。select th1.ID , th2.ID
from tmp_hotel th1
left join tmp_hotel th2
on th1.TELEPHONE = th1.TELEPHONE ;
结果如下:
from tmp_hotel th1
left join tmp_hotel th2
on th1.TELEPHONE = th1.TELEPHONE ;
结果如下:
select * from tmp_hotel th1 where th1.TELEPHONE in
(select th2.TELEPHONE from tmp_hotel th2 group by th2.TELEPHONE having count(*)>1);
left join tmp_hotel th2 on th1.TELEPHONE = th1.TELEPHONE 你不是th1和th2关联吗,关联字段写的怎么都是th1.TELEPHONE呢?不应该是th1.~ = th2.~ 吗?
不止是要判断是否重复 ,还要把重复的两条记录的id查询出来 。
from tmp_hotel th1
inner join tmp_hotel th2
on th1.TELEPHONE is not null and th2.TELEPHONE is not null
and th1.TELEPHONE = th2.TELEPHONE
and th1.ID != th2.ID
and (th1.DOTWID is not null or th2.DOTWID is not null)
;