如何确定与count（）汇总的非相同值？

Question

我有一个包含三列的表：城市ID，语言代码和城市：

city_id | language_code | city
----------------------------------
1       | en            | London
1       | es            | Londres
1       | pt            | Londres
2       | de            | Köln
2       | en            | Cologne
3       | it            | Cologne

在某些外语中，城市可以以相同的方式拼写，例如伦敦的西班牙语和葡萄牙名称为londres。

但是在某些情况下，相同名称可以指完全不同的位置，例如cologne是德国城市科伦市的英文名称，但意大利也有一个同名的小镇。

我希望能够检测表中有多个条目的城市，但只有链接到不同的city_id值的城市。在我的情况下，这将是cologne，但不是londres，因为es和pt语言版本指向相同的<代码> city_id 。

我认为这是一件相当容易的事情，但是我无法在单个查询中获得结果。取而代之的是，我首先删除结果，然后汇总它们：

WITH deduped_cities AS (
  SELECT DISTINCT city, city_id
  FROM cities
  ORDER BY city
)
SELECT city, COUNT(city_id) AS total
FROM deduped_cities
GROUP BY city
HAVING COUNT(city_id) > 1;

这给了我预期的结果：

city    | total
----------------
Cologne | 2

我只是想知道是否有可能使用单个选择语句实现相同的效果。

Answer 1

您基本上只有一个选择，但是可以使用Dinters'与count

SELECT city, COUNT(city_id) AS total
FROM cities
GROUP BY city
HAVING COUNT( DISTINCT city_id) > 1;

请参阅 fiddle

Answer 2

我相信您可以在汇总和有子句中进行独特之处：

select city, count (distinct city_id)
from cities
group by city
having count (distinct city_id) > 1