这是一个 * 标准 * 聚合+ group by + having问题。
示例数据：

SQL> with test (id, city) as
  2    (select 1, 'London' from dual union all
  3     select 2, 'Rome'   from dual union all
  4     select 3, 'London' from dual union all
  5     select 4, 'Rome'   from dual union all
  6     select 5, 'Zagreb' from dual
  7    )

查询：

8  select city,
  9         count(*) cnt
 10  from test
 11  group by city
 12  having count(*) > 1;

CITY          CNT
------ ----------
London          2
Rome            2

SQL>

case表达式与它有什么关系？

您可以使用条件聚合来获取输出：

SELECT COUNT(CASE city WHEN 'London' THEN 1 END) AS London,
       COUNT(CASE city WHEN 'Rome'   THEN 1 END) AS Rome
FROM   table_name
WHERE  city IN ('London', 'Rome')

其中，对于示例数据：

CREATE TABLE table_name (id, city) AS
SELECT 1, 'London' FROM DUAL UNION ALL
SELECT 2, 'Rome' FROM DUAL UNION ALL
SELECT 3, 'London' FROM DUAL UNION ALL
SELECT 4, 'Rome' FROM DUAL;

输出：
| 伦敦|罗马|
| - -|- -|
| 2个|2个|
但是，在这种情况下，如果值不重复，则不清楚如何处理对重复值的要求。
fiddle