postgresql语法错误，靠近join或任何其他

lstz6jyr 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(523)

我在postgresql数据库中导入了这个奥运会数据集。我想用select语句创建表，还有一些。但我得到了一个错误：
错误：foo.age列不存在第17行：（case when foo.age=null then foo.age=26 else foo.age end…^提示：也许你是想引用“ae.age”列。sql状态：42703字符：628
我要做的是：
更改列的类型年龄、身高、体重
获取上面的数据并将其与另一个查询连接起来，我希望在其中替换null值
使用以上两个数据创建新表。一气呵成。我还是这个数据库的新手，我实际上是在sqlite中做的，它工作得很好，但在这里它不会工作。如果你有别的办法，请告诉我。谢谢您！这是我的密码：

CREATE TABLE Female_participants AS 
SELECT DISTINCT
   ae.id AS Player_id,
   ae.Name,
   ( /* Here what i want to do is im thinking like foo table which i joined to this table is giving me 
       (this and following column where i want to replace NULL values) age,weight and height as int,float,float */
      CASE
         WHEN/*foo.age suppose to be column from foo table which is casted to int*/
            foo.Age = NULL 
         THEN
            foo.Age = 26 
         ELSE
            foo.Age 
      END
   )
   AS Age , 
   (
      CASE
         WHEN/*foo.height suppose to be float*/
            foo.height = NULL 
         THEN
            FLOOR(AVG(foo.height)) 
         ELSE
            foo.height 
      END
   )
   AS Height, 
   (
      CASE
         WHEN /*foo.Weight suppose to be float*/
            foo.weight = NULL 
         THEN
            FLOOR(AVG(foo.weight)) 
         ELSE
            foo.weight 
      END
   )
   AS weight, City, Team AS Country 
FROM
   athlete_events ae 
   INNER JOIN
      (
         SELECT DISTINCT
            id,
            /*Here i want to convert the dtype from TEXT to int or float*/
            CAST(age AS INT) thag,
            CAST(height AS FLOAT) AS thht,
            CAST(weight AS FLOAT) AS thwt 
         FROM
            athlete_events /* i read on https://www.postgresqltutorial.com/.. that i can self-join table */
      )
      AS foo /*Joining this table on id*/
      ON foo.id = ae.id 
GROUP BY
   id,
   name

sql postgresql Join column-alias

来源：https://stackoverflow.com/questions/63085922/postgresql-syntax-error-near-join-or-anyother

3条答案

按热度按时间

2w3kk1z51#

您的查询或多或少可以归结为：

SELECT DISTINCT
   ae.id AS Player_id,
   ae.Name,
   coalesce( foo.age::int ,26 ) ::int  AS Age , 
   coalesce( foo.height::float , FLOOR(AVG(foo.height::float ) over())::float ) ::float  AS Height , 
   coalesce( foo.weight::float , FLOOR(AVG(foo.weight::float ) over())::float ) ::float  AS Weight , 
   City,
   Team AS Country 
FROM
   athlete_events foo ;

请尝试阅读以下有关coalesce、双冒号cast和aggregate函数（不带groupby子句）的链接。

赞(0）回复(0）举报 2021-07-29

l3zydbqr2#

代码中有两个问题：
关系“foo”实际上没有列“age”，它的列是id、thag、thht、thwt
运算符“smth=null”不存在，必须使用smth is null或使用coalesce函数对于像这里这样的简单情况代码可以是这样的：

CREATE TABLE Female_participants AS 
    SELECT DISTINCT
       ae.id AS Player_id, 
       ae.Name, 
       COALESCE(foo.Age, 26) AS Age , 
       COALESCE(foo.height, foo_avg.height) AS Height, 
       COALESCE(foo.weight, foo_avg.weight) AS weight, 
       City, 
       Team AS Country 
    FROM athlete_events AS ae 
    INNER JOIN (
        SELECT DISTINCT
           id,
           age::INT,
           height::FLOAT,
           weight::FLOAT 
        FROM athlete_events
    ) AS foo ON foo.id = ae.id 
    INNER JOIN (
        SELECT
           AVG(height::FLOAT) AS height,
           AVG(weight::FLOAT) AS weight
        FROM athlete_events
    ) AS foo_avg ON TRUE;

赞(0）回复(0）举报 2021-07-29

9bfwbjaz3#

这个问题取决于这样一个事实：当子查询 thlete_avents 将列age、height和weight重命名为thag、thht、thwt。
因此，在您的示例块中，应该使用重命名的列名，而不是原始列名。
即

( /* Here what i want to do is im thinking like foo table which i joined to this table is giving me 
       (this and following column where i want to replace NULL values) age,weight and height as int,float,float */
      CASE
         WHEN/*foo.age suppose to be column from foo table which is casted to int*/
            foo.thag= NULL 
         THEN
            foo.thag = 26 
         ELSE
            foo.thag
      END
   )
   AS Age , 
   (
      CASE
         WHEN/*foo.height suppose to be float*/
            foo.thht= NULL 
         THEN
            FLOOR(AVG(foo.thht)) 
         ELSE
            foo.thht
      END
   )
   AS Height, 
   (
      CASE
         WHEN /*foo.Weight suppose to be float*/
            foo.thwt = NULL 
         THEN
            FLOOR(AVG(foo.thwt )) 
         ELSE
            foo.thwt 
      END
   )
   AS weight

赞(0）回复(0）举报 2021-07-29

我来回答

postgresql语法错误，靠近join或任何其他

3条答案

相关问题

热门标签

最新问答