子查询FROM子句在MySQL中失败

Question

在尝试运行FROM子句中的嵌套子查询时，我偶然发现了MySQL (v.8)的奇怪行为。我正在使用的示例数据库的(相关部分)架构如下：

​

​

以下两个查询在Server上运行相同：

SELECT SUM(tot) as total
 FROM (
     SELECT 
        SUM(OD.quantityOrdered * OD.priceEach) as tot,
        C.customerNumber
     FROM customers C
     INNER JOIN orders O ON C.customerNumber = O.customerNumber
     INNER JOIN orderdetails OD ON O.orderNumber = OD.orderNumber
     GROUP BY O.orderNumber, C.customerNumber
) AS CO
GROUP BY CO.customerNumber;

和

SELECT 
  (
    SELECT SUM(tot) as total
    FROM 
        (
            SELECT 
                (
                    SELECT  SUM(OD.quantityOrdered * OD.priceEach)
                    FROM orderdetails OD 
                    WHERE OD.orderNumber = O.orderNumber
                ) AS tot
            FROM orders O
            WHERE O.customerNumber = C.customerNumber
        ) AS ORD
  ) AS total
FROM customers AS C;

但是，在MySQL上，第一个运行良好，而第二个则导致一个错误：

Error Code: 1054. Unknown column 'C.customerNumber' in 'where clause'

我会感激任何关于为什么会发生这种事的线索。请注意，我最感兴趣的不是解决方法或其他实现此查询的方法，而是理解嵌套查询失败的原因。

Answer 1

查询范围内的C表别名

尝试使用联接重构查询

例如

select  c.customerNumber,  t.my_tot 
FROM customers AS C
INNER JOIN (

  SELECT  O.customerNumber, SUM(OD.quantityOrdered * OD.priceEach) my_tot
  FROM orderdetails OD 
  INNER JOIN orders O ON  OD.orderNumber = O.orderNumber
  GROUP BY O.customerNumber
) t on t.customerNumber = c.customerNumber

或

select  t.my_tot 
FROM customers AS C
INNER JOIN (

  SELECT  O.customerNumber, SUM(OD.quantityOrdered * OD.priceEach) my_tot
  FROM orderdetails OD 
  INNER JOIN orders O ON  OD.orderNumber = O.orderNumber
  GROUP BY O.customerNumber
) t on t.customerNumber = c.customerNumber

Answer 2

你可以在下面这样做

SELECT 
  (   
      select SUM(tot) as total from

          (   
            SELECT 
                (
                    SELECT  SUM(OD.quantityOrdered * OD.priceEach)
                    FROM orderdetails OD 
                    WHERE OD.orderNumber = O.orderNumber
                ) AS tot,customerNumber
            FROM orders O
           )  as ord      
            WHERE ord.customerNumber = C.customerNumber        
  ) AS total
FROM customers AS C;

您的customers表不属于子查询的范围，您在where条件WHERE O.customerNumber = C.customerNumber中使用了该表，因此我对它做了别名，然后在以后的级别中使用了customers表具有作用域的相同条件。

Answer 3

在第二种情况下，有一个相关的子查询。然而，关联子句有两个层次的深度。

即使在嵌套多个级别时，许多数据库仍将识别c。然而，MySQL (以及我认为是MS )是一个将关联子句限制在一个深度的数据库。