MySQL：从内联子查询返回多列

Question

我正在创建一条SQL语句，该语句将按月返回销售摘要。

该摘要将列出一些简单的列，用于显示日期，总销售数量和总销售价值。

但是，除了这些列之外，我还要再html" target="_blank">添加3个，以按花费金额列出最佳客户月份。对于这些列，我需要某种内联子查询，这些子查询可以返回其ID，名称和所花费的金额。

我目前的工作是使用内联SELECT语句，但是，根据我对如何实现这些语句的了解，每个内联语句只能返回一个列和一行。

为了解决我的情况，我当然可以创建3个单独的内联语句，但是，除了这似乎不切实际之外，它还会使查询时间增加所需要的时间。

SELECT  
    DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth,
    COUNT(OrderID) AS TotalOrders, 
    SUM(OrderTotal) AS TotalAmount,

    (SELECT SUM(OrderTotal) FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS TotalCustomerAmount,

    (SELECT OrderCustomerFK FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerID,

    (SELECT CustomerName FROM Orders INNER JOIN Customers ON OrderCustomerFK = CustomerID WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerName

FROM Orders     
GROUP BY DATE_FORMAT(OrderDate,'%m%y')
ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC

我如何更好地构造此查询？

完整答案

在对Dave Barkers解决方案进行了一些调整之后，我为将来寻求帮助的任何人提供了最终版本。

戴夫·巴克（Dave Barker）的解决方案与客户的详细信息完美配合，但是，使“总销售额”和“总销售额”列更为简单的数字却有些疯狂。

SELECT  
        Y.OrderMonth,   Y.TotalOrders,  Y.TotalAmount,
        Z.OrdCustFK,  Z.CustCompany,    Z.CustOrdTotal, Z.CustSalesTotal


 FROM 
        (SELECT
            OrdDate,
            DATE_FORMAT(OrdDate,'%M %Y') AS OrderMonth, 
            COUNT(OrderID) AS TotalOrders, 
            SUM(OrdGrandTotal) AS TotalAmount
            FROM Orders
            WHERE OrdConfirmed = 1    
            GROUP BY DATE_FORMAT(OrdDate,'%m%y') 
            ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC)
    Y INNER JOIN 
        (SELECT 
            DATE_FORMAT(OrdDate,'%M %Y') AS CustMonth, 
            OrdCustFK, 
            CustCompany, 
            COUNT(OrderID) AS CustOrdTotal,
            SUM(OrdGrandTotal) AS CustSalesTotal 
        FROM Orders INNER JOIN CustomerDetails ON OrdCustFK = CustomerID
        WHERE OrdConfirmed = 1
        GROUP BY DATE_FORMAT(OrdDate,'%m%y'), OrdCustFK 
        ORDER BY SUM(OrdGrandTotal) DESC) 
    Z ON Z.CustMonth = Y.OrderMonth

GROUP BY DATE_FORMAT(OrdDate,'%Y%m')
ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC

Answer 1

将内联SQL移为内部联接查询。所以你会有类似…

SELECT  DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth, COUNT(OrderID) AS TotalOrders, SUM(OrderTotal) AS TotalAmount,  Z.OrderCustomerFK, Z.CustomerName, z.OrderTotal as CustomerTotal   
  FROM Orders     
  INNER JOIN (SELECT DATE_FORMAT(OrderDate,'%M %Y') as Mon, OrderCustomerFK, CustomerName, SUM(OrderTotal) as OrderTotal 
                FROM Orders 
               GROUP BY  DATE_FORMAT(OrderDate,'%M %Y'), OrderCustomerFK, CustomerName ORDER BY SUM(OrderTotal) DESC LIMIT 1) Z
          ON Z.Mon = DATE_FORMAT(OrderDate,'%M %Y')
    GROUP BY DATE_FORMAT(OrderDate,'%m%y'), Z.OrderCustomerFK, Z.CustomerName
    ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC