将逻辑压缩为一个Sql查询

SELECT
 CityName,
 COUNT(*) AS CityListings
FROM
 YourTable
GROUP BY
 CityName
ORDER BY
 CityName

选择城市,从表中按城市分组计数(*)

;) 获取一本好的SQL书籍,这是“SQL in 21 hours”级别;)

按城市名称分组

你在找 Group By

SELECT city, COUNT(city)
FROM rentals
GROUP BY city
ORDER BY city

首先,试试这个

SELECT City, Count(*)
FROM myTable
GROUP BY City

你是说像下面这样的事情吗?

select city, count(city) from listings
group by city

这应该变成:

Shepparton blah blah blah
Mildura    yada yada yada
Shepparton dum  de   dum

分为:

Shepparton 2
Mildura    1

听起来你就是在追求这个。

SELECT DISTINCT city_name, count(*)
FROM rentals
GROUP BY city_name