mysql - 基于条件 SELECT 的 COUNT

Question

我有一个表，其中包含两个变量，每个变量都出现多次:

即

Customer        City
Bob             New York
Bob             Denver
Bob             Denver
Greg            Denver
Greg            Denver
Greg            New York

示例:

SELECT Customer FROM tableName WHERE COUNT( SELECT Customer,City FROM tableName)=1;

我想为每个唯一的客户名称获取唯一的城市，并计算该客户拥有多少个城市。你如何在 mysql 中执行此操作？

Answer 1

我不确定你是想为每个客户获取一个城市的重复次数还是不同城市的总数，所以我将同时输入这两个值:

select Customer, City, count(City) as CityCount, TotalDistinctCities
from CustomerCities
join (select Customer as cust, count(distinct City) as TotalDistinctCities
  from CustomerCities
  group by Customer)
as CityCount on cust=Customer   
group by Customer, City, TotalDistinctCities