SQL 查询不能在 FROM 语句中使用变量

Question

我是SQL的新手，很抱歉提出这个愚蠢的问题。

表将来自此SQL沙盒：https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

有格式的表格

OrderDetailID   OrderID ProductID   Quantity
1               10248   11          12
2               10248   42          10
3               10248   72          5
4               10249   14          9
5               10249   51          40

我想得到平均数量最大的产品。

我可以使用以下查询得到这个：

SELECT avg.ProductID, avg.Quantity
FROM (
SELECT ProductID, AVG(Quantity) Quantity
FROM OrderDetails
GROUP BY ProductID
) avg
WHERE avg.Quantity = (
SELECT MAX(Quantity) FROM (
SELECT ProductID, AVG(Quantity) Quantity
FROM OrderDetails
GROUP BY ProductID
)
)
ProductID   Quantity
8           70
48          70

这里我两次使用块

SELECT ProductID, AVG(Quantity) Quantity
FROM OrderDetails
GROUP BY ProductID

因为如果我使用avg查询而不是第二块

SELECT avg.ProductID, avg.Quantity
FROM (
SELECT ProductID, AVG(Quantity) Quantity
FROM OrderDetails
GROUP BY ProductID
) avg
WHERE avg.Quantity = (SELECT MAX(Quantity) FROM avg)

我得到错误could not prepare statement (1 no such table: avg)

所以我的问题是：

1. 这是一种语法错误，可以简单地纠正吗？还是由于某种原因，我不能使用这样的变量
2. 有没有更简单的方法来进行我需要的查询

Answer 1

考虑使用WITH子句的公共表表达式(CTE(，该子句允许您避免重复和重新计算聚合子查询。大多数RDBMS都支持CTE(在您的SQLTryIt链接页面中完全有效(。

WITH avg AS (
SELECT ProductID, AVG(Quantity) Quantity
FROM OrderDetails
GROUP BY ProductID
)
SELECT avg.ProductID, avg.Quantity
FROM avg
WHERE avg.Quantity = (
SELECT MAX(Quantity) FROM avg
)

Answer 2

1. 这并不是一个真正的语法问题，而是一个范围：引用不在父子关系中的别名。只有这样他们才能互相参考。(那里的标识符是一个别名，而不是一个变量——这是另一回事。(

2. 一种更简单的方法是在运行筛选条件之前创建一个临时集——就像前面的答案一样，使用CTE，或者可以尝试使用临时表。它们可以在任何地方使用，因为它们的作用域不在子查询中。