如何不使用JOIN，通过子查询实现指定国家航线的航空公司统计

原SQL通过JOIN关联多表，可统计法国、德国、西班牙、意大利四国目的地机场对应的不同航空公司数量。尝试改用子查询实现时，出现报错：ERROR: missing FROM-clause entry for table "countries"。

原JOIN查询代码 #

SELECT countries.name AS CountryName, COUNT(DISTINCT airlines.id)AS DistinctAirlinesCount 
FROM routes
JOIN airports ON routes.destination_airport_id = airports.id
JOIN airlines ON routes.airline_id = airlines.id
JOIN countries ON airports.country_id = countries.id
WHERE countries.name IN('France', 'Germany', 'Spain', 'Italy')
GROUP BY countries.name;

错误的子查询代码 #

SELECT countries.name AS CountryName, COUNT(DISTINCT airlines.id) AS DistinctAirlinesCount 
FROM routes
WHERE destination_airport_id IN (
    SELECT airports.id
    FROM airports
    WHERE airports.country_id IN (
        SELECT id
        FROM countries
        WHERE countries.name IN ('France', 'Germany', 'Spain', 'Italy')
    )
)
GROUP BY countries.name;

报错原因 #

改写的子查询中，SELECT和GROUP BY语句引用了countries.name，但FROM子句仅包含routes表，未在查询中关联countries表，数据库无法找到该表的引用，因此抛出错误。

正确的子查询实现方式 #

以下是两种不依赖显式JOIN关键字的子查询写法：

写法一：使用标量子查询获取字段值

SELECT 
    (SELECT c.name 
     FROM countries c 
     WHERE c.id = (SELECT a.country_id FROM airports a WHERE a.id = r.destination_airport_id)) AS CountryName,
    COUNT(DISTINCT (SELECT al.id FROM airlines al WHERE al.id = r.airline_id)) AS DistinctAirlinesCount
FROM routes r
WHERE EXISTS (
    SELECT 1 
    FROM airports a 
    JOIN countries c ON a.country_id = c.id
    WHERE a.id = r.destination_airport_id
      AND c.name IN ('France', 'Germany', 'Spain', 'Italy')
)
GROUP BY CountryName;

写法二：结合隐式关联与多层子查询过滤

SELECT 
    (SELECT name FROM countries WHERE id = (SELECT country_id FROM airports WHERE id = routes.destination_airport_id)) AS CountryName,
    COUNT(DISTINCT airlines.id) AS DistinctAirlinesCount
FROM routes, airlines
WHERE routes.airline_id = airlines.id
  AND routes.destination_airport_id IN (
      SELECT id FROM airports WHERE country_id IN (
          SELECT id FROM countries WHERE name IN ('France', 'Germany', 'Spain', 'Italy')
      )
  )
GROUP BY CountryName;

说明 #

• 两种写法均通过子查询获取countries表的名称字段，同时确保仅统计目标国家的机场数据。
• COUNT(DISTINCT ...)用于确保统计的是不同航空公司的数量，避免重复计数。

内容的提问来源于stack exchange，提问作者NicholasLorey