MySQL:从内联子查询返回多列 [英] MySQL: Returning multiple columns from an in-line subquery

问题描述

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

I'm creating an SQL statement that will return a month by month summary on sales.

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

The summary will list some simple columns for the date, total number of sales and the total value of sales.

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

However, in addition to these columns, i'd like to include 3 more that will list the months best customer by amount spent. For these columns, I need some kind of inline subquery that can return their ID, Name and the Amount they spent.

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

My current effort uses an inline SELECT statement, however, from my knowledge on how to implement these, you can only return one column and row per in-line statement.

要在我的场景中解决此问题，我当然可以创建3个单独的内联语句，但是，除了这似乎不切实际之外，它还会使查询时间增加所需的时间.

To get around this with my scenario, I can of course create 3 separate in-line statements, however, besides this seeming impractical, it increases the query time more that necessary.

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

我如何更好地构建此查询?

How can i better structure this query?

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

After some tweaking of Dave Barkers solution, I have a final version for anyone in the future looking for help.

Dave Barker的解决方案可以很好地与客户详细信息配合使用，但是，使总销售额"和总销售额"列更为简单的数字却有些疯狂.

The solution by Dave Barker worked perfectly with the customer details, however, it made the simpler Total Sales and Total Sale Amount columns get some crazy figures.

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

推荐答案

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

Move the inline SQL to be a inner join query. So you'd have something like...

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

这篇关于MySQL:从内联子查询返回多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！