SQL多表列平均值计算：ORA-00904错误排查与修正

需求说明 #

需要生成一张新表，包含每个国家的名称，以及按规则计算的平均人口值：将该国所有城市人口总和与country表中记录的该国人口值相加后取平均值。涉及两张核心表：

• city表：包含name、population、countrycode字段
• country表：包含code、name、population字段

错误SQL与报错信息 #

用户最后尝试的SQL语句：

select country.name, (added + country.population) / 2 as avg_population
from (select country.name, country.population, added 
      from country
      join (select country.name, sum(city.population) as added
            from city
            join country on city.countrycode = country.code
            group by country.name) on country.name = country.name);

报错信息：

ORA-00904: "COUNTRY"."CODE": 标识符无效
00904. 00000 - "%s: invalid identifier"
*原因:
*操作:
错误行：97，列：12

表结构参考 #

CREATE TABLE country 
(
  Code char(3) DEFAULT '' NOT NULL,
  Name char(52) DEFAULT '' NOT NULL,
  Continent varchar(15) DEFAULT 'Asia' NOT NULL check(Continent IN('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')),
  Region char(26) DEFAULT '' NOT NULL,
  SurfaceArea number(10,2) DEFAULT 0.00 NOT NULL,
  IndepYear number(6) DEFAULT NULL,
  Population number(11) DEFAULT 0 NOT NULL ,
  LifeExpectancy number(3,1) DEFAULT NULL,
  GNP number(10,2) DEFAULT NULL,
  GNPOld number(10,2) DEFAULT NULL,
  LocalName char(100) DEFAULT '' NOT NULL ,
  GovernmentForm char(45) DEFAULT '' NOT NULL ,
  HeadOfState char(60) DEFAULT NULL,
  Capital number(11) DEFAULT NULL,
  Code2 char(2)DEFAULT '' NOT NULL ,
  PRIMARY KEY (Code)
);

CREATE TABLE city (
  ID number(10) NOT NULL,
  Name char(35) DEFAULT '' NOT NULL,
  CountryCode char(3) DEFAULT '' NOT NULL,
  District char(30) DEFAULT '',
  Population number(10) DEFAULT 0 NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (CountryCode) REFERENCES country (Code)
);

CREATE TABLE countrylanguage (
  CountryCode CHAR(3) DEFAULT '' NOT NULL,
  Language CHAR(30) DEFAULT '' NOT NULL,
  IsOfficial char(1) DEFAULT 'F' NOT NULL check(IsOfficial IN('T','F')),
  Percentage number(4,1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (CountryCode,Language),
  FOREIGN KEY (CountryCode) REFERENCES country (Code)
) ;

错误原因 #

1. 表别名缺失导致字段歧义：内层子查询和外层均直接引用country表，未用别名区分，Oracle无法确定country.code所属的表实例，触发ORA-00904错误。
2. 关联条件无效：使用country.name = country.name作为关联条件，属于恒真条件，会产生笛卡尔积，且国家名称可能重复，无法实现正确关联。
3. 未处理无城市的国家：使用INNER JOIN会过滤掉没有城市记录的国家，不符合“每个国家”的需求。

正确SQL示例 #

方式一：创建新表（满足需求） #

CREATE TABLE country_avg_population AS
SELECT
    c.name AS country_name,
    (NVL(ci.total_city_pop, 0) + c.population) / 2 AS avg_population
FROM country c
LEFT JOIN (
    SELECT
        countrycode,
        SUM(population) AS total_city_pop
    FROM city
    GROUP BY countrycode
) ci ON c.code = ci.countrycode;

方式二：仅查询结果（不创建表） #

SELECT
    c.name AS country_name,
    (NVL(ci.total_city_pop, 0) + c.population) / 2 AS avg_population
FROM country c
LEFT JOIN (
    SELECT
        countrycode,
        SUM(population) AS total_city_pop
    FROM city
    GROUP BY countrycode
) ci ON c.code = ci.countrycode;

说明 #

• 内层子查询仅从city表分组统计，通过countrycode与外层country表关联，避免表名歧义。
• 使用LEFT JOIN确保所有国家都被包含，无城市的国家用NVL将城市人口总和转为0，避免计算结果为NULL。
• 按需求计算平均值，逻辑清晰且符合Oracle语法规范。

内容的提问来源于stack exchange，提问作者Daniel Felipe Castro Moreno