SQL中WITH子句定义avg_quantity却提示‘no such column’的原因咨询

你遇到的问题核心是CTE和标量子查询在引用方式上的差异：

1. 错误写法的问题所在
   你的CTE avg_quantity 定义的是一个临时表（虽然它只有一行一列），但在WHERE子句里直接写 OrderDetails.Quantity > avg_quantity 时，数据库会把avg_quantity识别为列名，而当前关联的OrderDetails和Products表中并没有这个列，所以抛出"no such column: avg_quantity"的错误。

2. 子查询写法能运行的原因
   而你用的子查询 (SELECT AVG(OrderDetails.Quantity) FROM OrderDetails) 是一个标量子查询——它会返回单个数值，数据库可以直接将这个数值与OrderDetails.Quantity做比较，不需要额外的引用方式。

3. 修正CTE写法的两种方式
   如果想继续用CTE实现需求，可以这样改：

• 方式一：在WHERE子句中通过子查询引用CTE的结果

WITH avg_quantity AS (SELECT AVG(OrderDetails.Quantity) AS avg_qty FROM OrderDetails)
SELECT Products.ProductName, OrderDetails.Quantity
FROM OrderDetails
JOIN Products ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.Quantity > (SELECT avg_qty FROM avg_quantity)

• 方式二：将CTE与主查询做JOIN关联

WITH avg_quantity AS (SELECT AVG(OrderDetails.Quantity) AS avg_qty FROM OrderDetails)
SELECT Products.ProductName, OrderDetails.Quantity
FROM OrderDetails
JOIN Products ON Products.ProductID = OrderDetails.ProductID
JOIN avg_quantity ON OrderDetails.Quantity > avg_quantity.avg_qty

注意给CTE里的聚合结果起别名（比如avg_qty），能让代码更清晰易读。

内容的提问来源于stack exchange，提问作者guilerme_coppola84