MySQL Sakila 与 World 练习答案（二）：W01～W16

对应题目：mysql-sakila-world-query-exercises.md。

分册导航：Sakila S01～S24 · 性能与综合接口

W01 国家详情及首都

SELECT
  co.Code AS country_code,
  co.Name AS country_name,
  co.Continent AS continent,
  co.Region AS region,
  co.Population AS population,
  co.LifeExpectancy AS life_expectancy,
  ci.Name AS capital_city
FROM country AS co
LEFT JOIN city AS ci ON ci.ID = co.Capital
WHERE co.Code = :country_code;

Capital 指向 city.ID；LEFT JOIN 保留 Capital 为 NULL 的国家。

W02 国家城市分页

SELECT ID, Name, District, Population
FROM city
WHERE CountryCode = :country_code
ORDER BY Population DESC, ID DESC
LIMIT :page_size OFFSET :offset;

mysql2 是否允许绑定 LIMIT 参数取决于所用调用方式和 prepared statement；不要为此拼接任意字符串。页大小和 offset 应先验证为非负整数。深分页见 W16/P03。

W03 洲内人口区间国家

SELECT Code, Name, Continent, Region, Population
FROM country
WHERE Continent = :continent
  AND Population BETWEEN :minimum_population AND :maximum_population
ORDER BY Population DESC, Code;

BETWEEN 包含两端，Population 参数必须按数字绑定。

W04 全球人口最多城市

SELECT
  ci.ID AS city_id,
  ci.Name AS city_name,
  co.Code AS country_code,
  co.Name AS country_name,
  ci.Population AS population
FROM city AS ci
JOIN country AS co ON co.Code = ci.CountryCode
ORDER BY ci.Population DESC, ci.ID
LIMIT 20;

W05 国家名称前缀

SELECT Code, Name, Continent, Region
FROM country
WHERE Name LIKE CONCAT(:name_prefix, ''%'')
ORDER BY Name, Code
LIMIT 20;

若百分号和下划线应作为普通字符，应用层需按约定转义 LIKE 元字符。

W06 国家官方语言

SELECT
  CountryCode AS country_code,
  Language AS language,
  Percentage AS percentage
FROM countrylanguage
WHERE CountryCode = :country_code
  AND IsOfficial = ''T''
ORDER BY Percentage DESC, Language;

World 的 IsOfficial 是 ENUM(‘‘T’’,‘‘F’’)，不是数字布尔值。

W07 没有官方语言的国家

NOT EXISTS：

SELECT co.Code, co.Name
FROM country AS co
WHERE NOT EXISTS (
  SELECT 1
  FROM countrylanguage AS cl
  WHERE cl.CountryCode = co.Code
    AND cl.IsOfficial = ''T''
)
ORDER BY co.Code;

LEFT JOIN：

SELECT co.Code, co.Name
FROM country AS co
LEFT JOIN countrylanguage AS cl
  ON cl.CountryCode = co.Code
 AND cl.IsOfficial = ''T''
WHERE cl.CountryCode IS NULL
ORDER BY co.Code;

IsOfficial 条件必须放在 ON；放 WHERE 会破坏保留空匹配的语义。

W08 没有城市记录的国家

SELECT co.Code, co.Name
FROM country AS co
WHERE NOT EXISTS (
  SELECT 1
  FROM city AS ci
  WHERE ci.CountryCode = co.Code
)
ORDER BY co.Code;

W09 每洲国家数量和人口

SELECT
  Continent AS continent,
  COUNT(*) AS country_count,
  SUM(Population) AS total_population,
  AVG(Population) AS average_population
FROM country
GROUP BY Continent
HAVING COUNT(*) >= :minimum_country_count
ORDER BY total_population DESC, continent;

AVG 返回精确类型/小数展示取决于输入和客户端映射，不应手工用整数除法替代。

W10 每国城市统计

SELECT
  co.Code AS country_code,
  co.Name AS country_name,
  COUNT(ci.ID) AS city_count,
  COALESCE(SUM(ci.Population), 0) AS city_population,
  co.Population AS country_population
FROM country AS co
LEFT JOIN city AS ci ON ci.CountryCode = co.Code
GROUP BY co.Code, co.Name, co.Population
ORDER BY co.Code;

COUNT(ci.ID) 不会把外连接产生的 NULL 占位行算作城市。

W11 最大城市人口占比

WITH ranked_cities AS (
  SELECT
    ci.CountryCode,
    ci.ID,
    ci.Name,
    ci.Population,
    ROW_NUMBER() OVER (
      PARTITION BY ci.CountryCode
      ORDER BY ci.Population DESC, ci.ID
    ) AS position_in_country
  FROM city AS ci
)
SELECT
  co.Code AS country_code,
  co.Name AS country_name,
  rc.Name AS largest_city_name,
  rc.Population AS largest_city_population,
  co.Population AS country_population,
  ROUND(rc.Population * 100.0 / NULLIF(co.Population, 0), 2)
    AS population_ratio_percent
FROM country AS co
LEFT JOIN ranked_cities AS rc
  ON rc.CountryCode = co.Code
 AND rc.position_in_country = 1
ORDER BY co.Code;

乘 100.0 明确得到百分比；NULLIF 防止除零。无城市国家返回 NULL 比例。

W12 至少两种官方语言

SELECT
  co.Code AS country_code,
  co.Name AS country_name,
  COUNT(cl.Language) AS official_language_count
FROM country AS co
JOIN countrylanguage AS cl
  ON cl.CountryCode = co.Code
 AND cl.IsOfficial = ''T''
GROUP BY co.Code, co.Name
HAVING COUNT(cl.Language) >= 2
ORDER BY official_language_count DESC, co.Code;

countrylanguage 的主键包含 CountryCode 和 Language，所以无需 DISTINCT。

W13 估算各语言使用人口

SELECT
  cl.Language AS language,
  COUNT(*) AS country_count,
  ROUND(SUM(co.Population * cl.Percentage / 100.0)) AS estimated_speaker_population
FROM countrylanguage AS cl
JOIN country AS co ON co.Code = cl.CountryCode
GROUP BY cl.Language
ORDER BY estimated_speaker_population DESC, language;

先汇总高精度表达式，最后再 ROUND，避免逐国提前舍入放大误差。这是基于样本比例的估算值。

W14 每国人口最多的三座城市

WITH ranked_cities AS (
  SELECT
    ci.ID,
    ci.Name,
    ci.CountryCode,
    ci.Population,
    ROW_NUMBER() OVER (
      PARTITION BY ci.CountryCode
      ORDER BY ci.Population DESC, ci.ID
    ) AS position_in_country
  FROM city AS ci
)
SELECT
  CountryCode AS country_code,
  ID AS city_id,
  Name AS city_name,
  Population AS population,
  position_in_country
FROM ranked_cities
WHERE position_in_country <= 3
ORDER BY CountryCode, position_in_country;

窗口别名不能在同层 WHERE 过滤，所以先放进 CTE。

W15 高于所在洲平均人口的国家

WITH countries_with_average AS (
  SELECT
    Code,
    Name,
    Continent,
    Population,
    AVG(Population) OVER (
      PARTITION BY Continent
    ) AS continent_average_population
  FROM country
)
SELECT
  Code AS country_code,
  Name AS country_name,
  Continent AS continent,
  Population AS population,
  continent_average_population
FROM countries_with_average
WHERE Population > continent_average_population
ORDER BY Continent, Population DESC, Code;

W16 游标分页城市

第一页：

SELECT ID, Name, CountryCode, District, Population
FROM city
ORDER BY Population DESC, ID DESC
LIMIT 20;

下一页把上一页最后一行的 Population 和 ID 作为游标：

SELECT ID, Name, CountryCode, District, Population
FROM city
WHERE (Population, ID) < (:last_population, :last_id)
ORDER BY Population DESC, ID DESC
LIMIT 20;

因为两个排序方向相同，行比较可直接使用小于。若排序方向混合，应展开成：

WHERE Population < :last_population
   OR (Population = :last_population AND ID < :last_id)

游标必须携带所有排序键，且数据在翻页期间变化时仍需接受“实时列表”语义或引入快照边界。