SQL命令未正确结束，与表别名有关 [英] SQL command not properly ended, concerning with table aliases

问题描述

我了解oracle不允许AS命令用于表别名，因此建议使用以下解决方案.

I understand that oracle does not allow AS commands for table aliases, so I've been suggested a solution like below.

SELECT Temp.avgsale
FROM (SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
     FROM orders o, parts p, odetails d
     GROUP BY o.received) Temp
WHERE Temp.avgsale=(SELECT MIN(Temp.avgsale) FROM Temp);

但是，运行它时出现以下错误:ERROR at line 4: ORA-00942: table or view does not exist.当我尝试在SELECT查询的开头设置别名时，也会出现类似的错误:Temp (SELECT ...).任何帮助将不胜感激，因为我在查询查询中出了什么问题时遇到了麻烦.

However, I am getting the following error when I run it: ERROR at line 4: ORA-00942: table or view does not exist. I also get similar errors when I try to set the alias at the beginning of the SELECT query: Temp (SELECT ...). Any help would be appreciated, as I'm having trouble as to what is wrong with the query.

推荐答案

问题在于，当解析where子句中的子查询时，可能尚未定义表别名temp.您可以改用公用表表达式:

The issue is that when the subquery in the where clause is parsed the table alias temp might not have been defined yet. You could use a common table expression instead:

WITH Temp AS (
    SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
    FROM orders o, parts p, odetails d
    GROUP BY o.received
    )

SELECT Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

查询似乎还有另一个问题；它缺少所需的orders，parts和odetails表之间的联接.您可能想要这样的东西:

It looks like the query has another issue though; it's missing joins between the orders, parts and odetails tables which are needed. You probably want something like this:

WITH Temp AS (
    SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
    FROM orders o
    JOIN odetails od ON o.orderid = od.orderid
    JOIN parts p ON p.partid = od.partid 
    GROUP BY o.received
    )

SELECT Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

请注意，我只是在猜测您的列名是什么，因为您未能在问题中包括有关此列的任何信息.

Note that I'm just guessing what your column names are as you failed to include any information about it in the question.

此外，也许您想要与min(temp.avgsale)匹配的订单，而不仅仅是min()?如果是这样，将最后一部分更改为:

Also, maybe you wanted the orders matching the min(temp.avgsale) and not just the min()? if so change the last part to:

SELECT Temp.received, Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

此查询对我来说更有意义.

This query would make more sense to me.

这篇关于SQL命令未正确结束，与表别名有关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！