SQLite:为每组实体检索单个代表实体

Question

如果我有一个包含加油站的表格，其中指定了城市、每个加油站的名称和纬度+经度:

city        | name         | latitude      | longitude
---------------------------------------------------------
Berlin      | GasPump1     | 52.5          | 13.5
Berlin      | GasPump2     | 52.52         | 13.51
Zagreb      | INA1         | 45.8          | 16
Zagreb      | INA2         | 45.81         | 16.01
Zagreb      | INA3         | 45.82         | 15.99

我将如何使用单个 SQL 查询为每个城市获取单个代表实体？也就是说，我将如何使用单个查询来实现以下伪代码:

representativeStationsArray = []
citiesArray = sqlQuery("SELECT DISTINCT city FROM gasstations")
for city in citiesArray:
    representativeStation = sqlQuery(
        "SELECT * FROM gasstations WHERE city = ? LIMIT 1;", 
        city)
    representativeStationsArray.append(representativeStation)

我已经接受了一个答案，但我认为向 future 的读者解释我的需求可能会很有趣。

在 iOS 上，一旦用户缩小， map View 上显示的注释过多会降低应用程序的速度。我正在尝试的第一个实验性解决方案(对于我正在开发的国家来说应该没问题)是按城市过滤注释，并为每个城市显示一个随机注释。

如果这证明不令人满意，我会寻找另一种解决方案。

Answer 1

如果“代表行”只是指“城市匹配的任意行”，那么您可以使用 GROUP BY:

SELECT * FROM gasstations
GROUP BY city

city 以外的字段将填充来自任意组匹配行的数据。

来自 http://sqlite.org/lang_select.html#resultset 的文档(强调我的):

If the SELECT statement is an aggregate query with a GROUP BY clause ...

Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.

注意:它适用于 SQLite 和 MySQL 以及其他一些数据库。但是许多(大多数？)数据库不允许您在分组查询中选择非聚合字段。因此，如果您曾经切换数据库或部署到不同的生产数据库，请注意这一点。