MS Access HW Query在某处错过了明显的错误？

I'm getting an error stating that I have not specified OrdID's table:

我收到一条错误,指出我没有指定OrdID的表:

Here's what I have:

这就是我所拥有的:

SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty)  
    FROM ((Cus INNER JOIN Orders ON Cus.CID=Orders.CID)
        INNER JOIN OrderLine
            ON Orders.OrdID=OrderLine.OrdID)
        INNER JOIN ProdFabric
            ON OrderLine.PrID=ProdFabric.PrID   
            AND OrderLine.Fabric=ProdFabric.Fabric  
    GROUP BY Last  
    ORDER BY Last DESC, OrdID DESC;

When I hit run, it keeps saying that OrdID could refer to more than one table listed in the FROM clause.

当我点击运行时,它一直说OrdID可以引用FROM子句中列出的多个表。

Why does it keep saying that as I've specified which table to select for OrdID.

为什么它一直这样说,因为我已经指定了为OrdID选择哪个表。

Tables:

Cus (**CID**, Last, First, Phone)  
Orders (**OrdID**, OrdDate, ShipDate, CID)  
Manu (**ManuID**, Name, Phone, City)  
Prods (**PrID**, ManuID, Category)  
ProdFabric (**PrID**, **Fabric**, Price)  
Orderline (**OrdId**, **PrID**, Fabric, Qty)

2 个解决方案

#1

Your ORDER BY clause is valid SQL-92 syntax.

您的ORDER BY子句是有效的SQL-92语法。

Sadly, the Access database engine is not SQL-92 compliant. It doesn't let you use a column correlation name ('alias') from the SELECT clause. If you had used this:

遗憾的是,Access数据库引擎不符合SQL-92。它不允许您使用SELECT子句中的列相关名称('别名')。如果您使用过此:

SUM(Price * Qty) AS total_price
...
ORDER BY total_price

you would have received an error. (Aside: you should consider giving this expression a column correlation name anyhow.)

你会收到一个错误。 (旁白:无论如何,你应该考虑给这个表达式一个列相关名。)

Instead of correlation names, the Access data engine is expecting either a column name or an expression (the latter being illegal in SQL-92); specified columns need not appear in the SELECT clause (again illegal in SQL-92). Because any column from any table in the FROM clause can be used, you need to disambiguate them with the table name; if you used a table correlation name in the FROM clause then you must use it in the ORDER BY clause (I don't make the rules!)

Access数据引擎不是使用相关名,而是期望列名或表达式(后者在SQL-92中是非法的);指定的列不需要出现在SELECT子句中(在SQL-92中也是非法的)。因为可以使用FROM子句中任何表的任何列,所以需要使用表名来消除它们的歧义;如果你在FROM子句中使用了表相关名,那么你必须在ORDER BY子句中使用它(我没有制定规则!)

To satisfy the Access database engine's requirements, I think you need to change your ORDER BY clause to this:

为了满足Access数据库引擎的要求,我认为您需要将ORDER BY子句更改为:

ORDER BY Last DESC, OrderLine.OrdID DESC;

As an aside, I think your code would be more readable if you qualify you columns with table names in your SELECT clause even when they are unambiguous in context (I find using full table names a little wordy and prefer short table correlation names, specified in the data dictionary and used consistently in all queries). As it stands I can only guess that OrdDate is from Orders, and Price and Qty are from OrderLine. I've not idea what Last represents.

顺便说一句,我认为如果你在SELECT子句中使用表名对列进行限定,你的代码将更具可读性,即使它们在上下文中是明确的(我发现使用完整的表名有点罗嗦而且更喜欢短表相关名,在数据字典并在所有查询中一致使用)。目前我只能猜测OrdDate来自Orders,而Price和Qty来自OrderLine。我不知道最后代表什么。

#2

The ORDER BY clause is agnostic to what you've specified in the SELECT list. Its possible for example to order by a field that you don't actually include in the output select list.

ORDER BY子句与您在SELECT列表中指定的内容无关。例如,可以通过您实际上不包含在输出选择列表中的字段进行排序。

Hence you need to be sure that the fields in the Order by list are not ambigious.

因此,您需要确保Order by列表中的字段不是ambigious。

#1