时间戳

首页 > 解决方案 > MYSQL：通过连接几个表来查询世界数据库

mysql - MYSQL：通过连接几个表来查询世界数据库

问题描述

问题是：

给出 SQL 命令以显示北美的每个国家：

结果应按城市名称字母顺序排列，在该范围内按国家名称顺序排列，在该范围内按语言顺序排列，在该范围内按百分比升序排列。获取错误消息不是唯一的表别名国家。

SELECT
  city.name AS name, country.name AS name,
  countrylanguage.language, 
  countrylanguage.percentage
FROM country, countrylanguage, city
INNER JOIN country ON city.countrycode = country.code
INNER JOIN city ON country.capital = city.id
INNER JOIN countrylanguage on country.code = countrylanguage.countrycode
WHERE
  country.continent = 'North America' and
  countrylanguage.isofficial = 'T' and 
  country.capital = city.id
ORDER BY
  city.name ASC,
  country.name,
  country.language,
  countrylanguage.percentage ASC;

标签： mysqlinner-join

解决方案

您的FROM ... JOIN 查询部分不正确。尝试这个。

SELECT
  city.name AS name, country.name AS name,
  countrylanguage.language, 
  countrylanguage.percentage
FROM country
INNER JOIN country ON city.countrycode = country.code
INNER JOIN city ON country.capital = city.id
INNER JOIN countrylanguage on country.code = countrylanguage.countrycode
WHERE
  country.continent = 'North America' and
  countrylanguage.isofficial = 'T' and 
  country.capital = city.id
ORDER BY
  city.name ASC,
  country.name ASC,
  country.language ASC,
  countrylanguage.percentage ASC;

当你说它FROM country, city, countrylanguage相当于上面的代码但没有ON子句时。这是老式的逗号连接语法（在 1992 年被显式 JOIN 语法取代）。

推荐阅读