首页 文章

在Oracle的Where子句子查询中使用别名或表名，

提问于
浏览 次
1

我需要在查询中显示其他表中的一些字段，我收到此错误：

ORA-00904：“THIS _” . “ID”：identificadorinválido00904 . 00000 - “％s：无效标识符”

这是查询

SELECT this_."ID" as ID1_47_2_
FROM "ENSAIO_AMOSTRA" this_
inner join "ETAPA_FLUXO_AMOSTRA" efe1_ on  this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"
inner join "ETAPA" et2_ on efe1_."ID_ETAPA"=et2_."ID"
WHERE et2_."ID" in (5) and not (et2_."ID" = 6)
and this_."ID_ENSAIO" = 835
and efe1_."ID" = (
    select y0_ from
    ( SELECT this_0_."ID" as y0_
      FROM "ETAPA_FLUXO_AMOSTRA" this_0_
      WHERE this_0_."ID_ENSAIO_AMOSTRA" = this_."ID"
      ORDER BY this_0_."ID" desc )
)

如果我为一个数字（已经存在的ensaio_amostra）改变了这个_ . “ID”，它就可以了 .

我该怎么办？

sql oracle oracle11g subquery

1 回答

  • 1
    and efe1_."ID" = (
    select y0_ from
    ( SELECT this_0_."ID" as y0_
      FROM "ETAPA_FLUXO_AMOSTRA" this_0_
      WHERE this_0_."ID_ENSAIO_AMOSTRA" = this_."ID"
      ORDER BY this_0_."ID" desc )
)

    您不需要 sub-query ，因为您已使用 INNER JOIN 子句加入了表：

    FROM "ENSAIO_AMOSTRA" this_
inner join "ETAPA_FLUXO_AMOSTRA" efe1_ on  this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"

    只需使用：

    SELECT this_."ID" AS ID1_47_2_
FROM "ENSAIO_AMOSTRA" this_
INNER JOIN "ETAPA_FLUXO_AMOSTRA" efe1_
ON this_."ID" = efe1_."ID_ENSAIO_AMOSTRA"
INNER JOIN "ETAPA" et2_
ON efe1_."ID_ETAPA"   =et2_."ID"
WHERE et2_."ID"      IN (5)
AND NOT (et2_."ID"    = 6)
AND this_."ID_ENSAIO" = 835;

    另外，您的子查询中的 ORDER BY 无意义 . 此外，如果您在子查询中获得多行，则无论如何都会失败 .

    回复于

相关问题