SELECT语句子查询无法识别已定义表别名s_enrollment的原因

你的判断完全准确，问题出在SQL的作用域规则：
FROM子句中定义的派生表别名s_enrollment，仅对当前外层查询的同级别子句（SELECT/WHERE/GROUP BY等）可见，无法被同级别WHERE里嵌套的独立子查询当作表来引用。你写的标量子查询select max(enrollment) from s_enrollment会独立查找名为s_enrollment的实体表，自然会报找不到表的错误。

写法1：CTE（公共表表达式） #

CTE定义的临时结果集支持在后续查询中多次引用，可读性最好，兼容绝大多数主流数据库：

WITH s_enrollment AS (
    select course_id, sec_id, count(id) as enrollment
    from takes natural join section
    where semester ='Fall' and year = 2009
    group by course_id, sec_id
)
select course_id, sec_id
from s_enrollment
where enrollment = (select max(enrollment) from s_enrollment);

写法2：窗口函数 #

不需要二次查询临时结果，执行效率更高，适合数据量较大的场景：

select course_id, sec_id
from (
    select 
        course_id, 
        sec_id, 
        count(id) as enrollment,
        max(count(id)) over () as max_enroll
    from takes natural join section
    where semester ='Fall' and year = 2009
    group by course_id, sec_id
) as s_enrollment
where enrollment = max_enroll;

takes表 #

CREATE TABLE takes
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     grade              varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references student
        on delete cascade
    )

section表 #

CREATE TABLE section
    (course_id      varchar(8), 
         sec_id         varchar(8),
     semester       varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year           numeric(4,0) check (year > 1701 and year < 2100), 
     building       varchar(15),
     room_number        varchar(7),
     time_slot_id       varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (building, room_number) references classroom
        on delete set null
    )

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