MySQL - 在 join 和 from 上使用子查询

debugcn 发表于 Dev

巴比什卡

我试图为所有订单的每个客户返回 CustomerID、CompanyName、OrderID 和小计，小计金额高于客户的平均小计金额。这些是我正在使用的表和下面的查询。我不确定我返回的值是否正确，希望有人可以帮助我了解它们是否基于我的查询。提前致谢。

Orders
 Columns
  OrderID
  CustomerID
  EmployeeID
  OrderDate
  RequiredDate

OrderDetails
 Columns
  OrderID
  ProductID
  UnitPrice
  Quantity

Products
 Columns
  ProductID
  ProductName
  QuantityPerUnit
  UnitPrice

Customers
 Columns
  CustomerID
  CompanyName
  ContactName
  Country





SELECT A.CustomerID, A.CompanyName, A.Subtotal, A.OrderID, AVGSubtotal
FROM (
    SELECT
        C.CustomerID,
        C.CompanyName,
        (D.UnitPrice * P.QuantityPerUnit) AS Subtotal,
        D.OrderID
    FROM Customers C
        JOIN Orders O ON C.CustomerID = O.CustomerID
        JOIN OrderDetails D ON D.OrderID = O.OrderID
        JOIN Products P ON P.ProductID = D.ProductID
    GROUP BY
        D.OrderID, C.CustomerID
) A
JOIN (
    SELECT
        S.CustomerID, S.CompanyName, AVG(S.Subtotal) as AVGSubtotal
    FROM (
        SELECT
            C.CustomerID,
            C.CompanyName,
            (D.UnitPrice * P.QuantityPerUnit) AS Subtotal
        FROM Customers C
            JOIN Orders O ON C.CustomerID = O.CustomerID
            JOIN OrderDetails D ON D.OrderID = O.OrderID
            JOIN Products P ON P.ProductID = D.ProductID
        GROUP BY
            D.OrderID, C.CustomerID
        ) S
    GROUP BY
        S.CustomerID
) B ON A.CustomerID = B.CustomerID 
WHERE
    A.CustomerID = B.CustomerID AND 
    A.Subtotal > B.AVGSubtotal 
ORDER BY
    A.CustomerID, A.CompanyName
;

列昂尼德·扎哈罗夫

select
  c2.customerID,
  c2.CompanyName,
  c2.AVGSubtotal
  o2.OrderID,
  o2.UnitPrice * o2.Quantity as subtotal
from (
  select
    c.CustomerID,
    c.CompanyName,
    sum(o.UnitPrice * o.Quantity)/count(*) as AVGSubtotal
  from
    Customers c
    inner join Orders o on (o.CustomerID = c.CustomerID)
    inner join OrderDetails od on (od.OrderID = c.OrderID)
  group by
    o.CustomerID  
) as c2
inner join Orders o2 on (o2.CustomerID = c2.CustomerID)
where o2.UnitPrice * o2.Quantity > c2.AVGSubtotal

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。