mysql - 如何解决“字段列表”中的未知列“x.y”

通过Netbeans“执行查询”功能在MySQL数据库上运行此查询时，出现以下错误。

Error code 1054, SQL state 42S22: Unknown column 'avgratingbyusers.avgrating' in 'field list'
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

从此代码
教程源：http://webdam.inria.fr/Jorge/html/wdmch19.html#x25-37800018.4

create table ratings (
   userid int,
   itemid int,
   rating int,
   timestamp int,
   primary key (userid, itemid));
create index usersratings_index on ratings (userid);
create index itemsratings_index on ratings (itemid);

insert into ratings (userid,itemid,rating,timestamp)
   (select ratingsdata.userid, ratingsdata.itemid,
        ratingsdata.rating-avgratingbyusers.avgrating,
        ratingsdata.timestamp
    from ratingsdata,
       (select userid, avg(rating)
        from ratingsdata
        group by userid
       ) as avgratingbyusers
    where ratingsdata.userid=avgratingbyusers.userid
   );

我该如何解决这个问题？

编辑 - - - - - - - - - - - - - - - - - - - - - - - - - --

尝试将查询更改为此

insert into `ratings` (`userid`,`itemid`,`rating`,`timestamp`)
 (select `ratingsdata.userid`, `ratingsdata.itemid`,
    `ratingsdata.rating-avgratingbyusers.avgrating`,
    `ratingsdata.timestamp `
 from `ratingsdata`,
   (select `userid`, avg(`rating`)
    from `ratingsdata`
    group by `userid`
   ) as `avgratingbyusers`
where `ratingsdata.userid`=`avgratingbyusers.userid`
);

背s。

现在出现以下错误

Error code 1054, SQL state 42S22: Unknown column 'ratingsdata.userid' in 'field list'
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

我该如何解决这个问题？

最佳答案

您需要在子查询别名中给列：

insert into ratings (userid,itemid,rating,timestamp)
   select rd.userid, rd.itemid, rd.rating-ru.avgrating, rd.timestamp
    from ratingsdata rd join
         (select userid, avg(rating) as avgrating
----------------------------------------^
          from ratingsdata
          group by userid
         ) ru
         on rd.userid = ru.userid ;

重要的变化是突出显示的变化。我进行了其他更改：


删除了select子句的括号。这些对于insert . . . select是不必要的。
将联接更改为使用显式联接而不是隐式联接。
引入了表别名（即表名的缩写），以使查询更具可读性。

关于mysql - 如何解决“字段列表”中的未知列“x.y”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23418608/