代码之家 › 专栏 › 技术社区 › Supertracer

使用alias mysql时出现未知列

alias subquery mysql

Supertracer · 技术社区 · 6 年前

我正在学习SQL课程 lagunita.satnford.edu . 我正在练习查询,我有三个表:

电影(中期、片名、年份、导演)

审阅者(rID,姓名)

评级(rID、mID、stars、ratingDate)

问题陈述: 找出1980年之前发行的电影的平均评级与1980年之后发行的电影的平均评级之间的差异。(确保计算每部电影的平均评分,然后是1980年之前和之后电影的平均评分。不要只计算1980年之前和之后的总体平均评分。) 我编写了以下查询:

select max(a1) - min(a1) from
(
    select avg(av1) from
        (
            select avg(stars) av1
            from rating join movie m using(mID)
            where year < 1980
            group by mID
        ) as av1
    union
    select avg(av2) from
        (
            select avg(stars) av2
            from rating join movie m using(mID)
            where year > 1980
            group by mID
        ) as av2
) as a1;

我收到以下错误 ERROR 1054 (42S22): Unknown column 'a1' in 'field list'

创建示例数据的命令:

/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

insert into Reviewer values(201, 'Sarah Martinez');
insert into Reviewer values(202, 'Daniel Lewis');
insert into Reviewer values(203, 'Brittany Harris');
insert into Reviewer values(204, 'Mike Anderson');
insert into Reviewer values(205, 'Chris Jackson');
insert into Reviewer values(206, 'Elizabeth Thomas');
insert into Reviewer values(207, 'James Cameron');
insert into Reviewer values(208, 'Ashley White');

insert into Rating values(201, 101, 2, '2011-01-22');
insert into Rating values(201, 101, 4, '2011-01-27');
insert into Rating values(202, 106, 4, null);
insert into Rating values(203, 103, 2, '2011-01-20');
insert into Rating values(203, 108, 4, '2011-01-12');
insert into Rating values(203, 108, 2, '2011-01-30');
insert into Rating values(204, 101, 3, '2011-01-09');
insert into Rating values(205, 103, 3, '2011-01-27');
insert into Rating values(205, 104, 2, '2011-01-22');
insert into Rating values(205, 108, 4, null);
insert into Rating values(206, 107, 3, '2011-01-15');
insert into Rating values(206, 106, 5, '2011-01-19');
insert into Rating values(207, 107, 5, '2011-01-20');
insert into Rating values(208, 104, 3, '2011-01-02');

1 回复 | 直到 6 年前

MAK 6 年前

请用样本数据发布问题,这样测试和回答都很容易。

在您的代码中 a1 是派生表的名称,而不是列名。

聚合函数根据列名接受参数。

请尝试以下操作:

select max(av) - min(av) from
(
    select avg(av1) av from
        (
            select avg(stars) av1
            from rating join movie m using(mID)
            where year < 1980
            group by mID
        ) as av1
    union
    select avg(av2) av from
        (
            select avg(stars) av2
            from rating join movie m using(mID)
            where year > 1980
            group by mID
        ) as av2
) as a1;