MySQL 选择计数/重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2516546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select count / duplicates
提问by mike
I have a table with all U.S. zip codes. each row contains the city and state name for the zip code. I'm trying to get a list of cities that show up in multiple states. This wouldn't be a problem if there weren't X amount of zip codes in the same city...
我有一张包含所有美国邮政编码的表格。每行包含邮政编码的城市和州名。我正在尝试获取出现在多个州的城市列表。如果在同一个城市没有 X 数量的邮政编码，这将不是问题......
So basically, I just want to the city in a state to count as 1 instead of it counting the city/state 7 times because there are 2+ zip codes in that city/state...
所以基本上，我只想将某个州的城市算作 1，而不是将城市/州计算 7 次，因为该城市/州有 2 个以上的邮政编码......
I'm not really sure how to do this. I know I need to use count but how do I tell the mysql to only count a given city/state combo as 1?
我真的不知道如何做到这一点。我知道我需要使用 count 但我如何告诉 mysql 只将给定的城市/州组合计为 1？
回答by Patrick Karcher
SELECT City, Count(City) As theCount
FROM (Select City, State From tblCityStateZips Group By City, State) As C
GROUP By City
HAVING COUNT Count(City) > 1
This would return all cities, with count, that were contained in more than one state.
这将返回包含在一个以上州的所有城市，包括计数。
Greenville 39
Greenwood 2
GreenBriar 3
etc.
格林维尔 39
格林伍德 2
绿蔷薇 3
等等。
回答by Guffa
First group on state and city, then group the result on city:
首先对州和城市进行分组，然后对城市的结果进行分组：
select City
from (
select State, City
from ZipCode
group by State, City
) x
group by City
having count(*) > 1
回答by Raj More
Will this do the trick
这能解决问题吗
Select CityName, Count (Distinct State) as StateCount
From CityStateTable
Group by CityName
HAVING Count (Distinct State) > 1
回答by Hyman Marchetti
You probably should have created a separate table for zip codes then to avoid the duplication.
您可能应该为邮政编码创建一个单独的表，然后避免重复。
You want to look into the GROUP BY Aggregate.
您想查看GROUP BY 聚合。
回答by Bryan Denny
Try using a select distinct
尝试使用 select distinct
SELECT DISTINCT city, state FROM table GROUP BY city
