时间戳

首页 > 解决方案 > 尝试学习 SQL，但我遇到了一个简单的查询

mysql - 尝试学习 SQL，但我遇到了一个简单的查询

问题描述

我刚刚安装了 MySQL，我对学习 SQL 很感兴趣。MySQL 的默认数据库之一是“World”。该数据库有 5 列：ID、名称（城市名称）、CountryCode、District 和 Population。我尝试了一个简单的查询，该查询应该显示每个国家/地区人口最少的城市的 ID。

USE world;

SELECT ID, MIN(POPULATION), COUNTRYCODE
FROM CITY
GROUP BY COUNTRYCODE
order by ID

问题是查询结果显示的是国家的第一个ID，与该国人口最少的城市ID无关。

示例：AFG 国家代码有 4 个城市：

+----+----------------+-------------+------------+ ------------+
| 身份证 | 城市 | 国家代码 | CITY_AGAIN | 人口 |
+----+----------------+-------------+------------+ ------------+
| 1 | 喀布尔 | AFG | 卡博尔 | 1780000 |
| 2 | 坎大哈 | AFG | 坎大哈 | 237500 |
| 3 | 赫拉特 | AFG | 赫拉特 | 186800 |
| 4 | 马扎里沙里夫 | AFG | 巴尔赫 | 127800 |
+----+----------------+-------------+------------+ ------------+

AFG国家的查询结果是

+----+------------+-------------+
| 身份证 | 人口 | 国家代码 |
+----+------------+-------------+
| 1 | 127800 | AFG |
+----+------------+-------------+

ID 应该是 4，而不是 1。

那么，为什么查询的结果在 ID 列上不正确呢？

标签： mysqlsql

解决方案

What your query does is:

  1. You select all rows from the city table.
  2. You aggregate those rows to get one result per country.
  3. You show the ID. The ID??? There are many cities in a country, hence many IDs. Which do you want to show? The minimum? The maximum? You don't tell the DBMS which. This should result in an error (and it would had you set sql mode to ONLY_FULL_GROUP_BY). As is, MySQL silently applies ANY_VALUE(id), i.e. it picks an ID arbitrarily which is not what you want.

You cannot do what you want in one step. You need two steps:

  1. Find the minimum poplulation per country.
  2. Find the cities matching that population.

One (simple) solution:

SELECT *
FROM city
WHERE (countrycode, population) IN
(
  SELECT countrycode, MIN(population)
  FROM city
  GROUP BY countrycode
);

Another solution follows a different path: Select all rows for which not exists a city with a lower population for the country:

SELECT *
FROM city
WHERE NOT EXISTS
(
  SELECT NULL
  FROM city smaller_city
  WHERE smaller_city.countrycode = city.countrycode
  AND smaller_city.population < city.population
);

推荐阅读