问如何从具有公共值和不同值的coloumn1(状态)和列(城市)的表中提取记录

EN

SELECT        States, COUNT(City) AS NoofCities
FROM            (SELECT        States, City
                          FROM            Geogrpahy
                          GROUP BY States, City) AS TT
GROUP BY States  having COUNT(City)>1

select States,City from Geogrpahy  as geography1 where City in (select City from Geogrpahy where Geogrpahy.States!=geography1.States)   

--Microsoft SQL Specific:
--States with multiple cities
SELECT [State] ,count (distinct [City]) as Cities_Count
   FROM [dbo].[Geogrpahy]
   group by [State]
   having count (distinct [City]) > 1


--Cities in multiple States

select * from [Geogrpahy]
where [City] in (
select [City] from (
SELECT [City] ,count (distinct [State]) as State_Count
       FROM [dbo].[Geogrpahy]
       group by [City]
       having count (distinct [State]) > 1) a)
order by City, [State]

SELECT State  , COUNT(*) AS NO_OF_City FROM Geogrpahy 
GROUP BY State  HAVING COUNT(*) > 1 ORDER BY State  ;