如果你的cityID是主鍵的話可以 select * from cities_cities a
where exists(select * from cities_cities
where cityname=a.cityname and province=a.province and cityID<>a.cityID)
where exists(select * from cities_cities
where cityname=a.cityname and province=a.province and cityID<>a.cityID)
select *,row_number() over(partition by cityname,province order by getdate()) as rn
from cities_cities
) t
where rn>1
SQL:
select * from cities_cities a where EXISTS
(select cityname,province from cities_cities
WHERE a.cityname = cityname AND a.province =province
group by cityname,province Having Count(*)>1)