选择计数/重复 [英] Select count / duplicates

问题描述

我有一个包含所有美国邮政编码的表格。每行包含邮政编码的城市和州名称。我想获得显示在多个州的城市列表。这不会是一个问题，如果在同一个城市没有X个邮政编码...

所以基本上，我只想在城市状态计为1，而不是计数城市/州7次，因为在那个城市/州有2+邮政编码...

我不是真的肯定如何做到这一点。我知道我需要使用count，但是如何告诉mysql只计算给定的城市/状态组合为1？

解决方案

 选择城市，计数（城市）作为theCount 
 FROM（选择城市，州从tblCityStateZips组按城市，州）As C 
按城市
 HAVING COUNT Count（City）> 1 
  

这将返回包含在多个州的所有城市， p>

格林维尔39

格林伍德2

GreenBriar 3

等。

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...

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...

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?

解决方案

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.

这篇关于选择计数/重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！