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

MySQL: Returning multiple columns from an in-line subquery

提问by ticallian

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

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

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

摘要将列出日期、销售总数和销售总值的一些简单列。

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.

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

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

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

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.

为了在我的场景中解决这个问题，我当然可以创建 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

How can i better structure this query?

我怎样才能更好地构建这个查询？

---

---

FULL ANSWER

完整答案

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

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

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.

Dave Barker 的解决方案与客户详细信息完美配合，但是，它使更简单的 Total Sales 和 Total Sale Amount 列获得了一些疯狂的数字。

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

采纳答案by Dave Barker

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

将内联 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

回答by Julian

You can also do something like:

您还可以执行以下操作：

SELECT 
    a.`y`,
    ( SELECT @c:=NULL ) AS `temp`,
    ( SELECT @d:=NULL ) AS `temp`,
    ( SELECT 
          CONCAT(@c:=b.`c`, @d:=b.`d`) 
      FROM `b`
      ORDER BY b.`uid` 
      LIMIT 1 ) AS `temp`,
    @c as c,
    @d as d
 FROM `a`

回答by OMG Ponies

Give this a shot:

试一试：

  SELECT CONCAT(o.order_month, ' ', o.order_year),
         o.total_orders,
         o.total_amount,
         x.sum_order_total,
         x.ordercustomerfk,
         x.customername
    FROM (SELECT MONTH(t.orderdate) AS order_month,
                 YEAR(t.orderdate) AS order_year
                 COUNT(t.orderid) AS total_orders, 
                 SUM(t.ordertotal) AS total_amount
            FROM ORDERS t
        GROUP BY MONTH(t.orderdate), YEAR(t.orderdate)) o
    JOIN (SELECT MONTH(t.orderdate) AS ordermonth,
                 YEAR(t.orderdate) AS orderyear
                 SUM(t.ordertotal) 'sum_order_total',
                 t.ordercustomerfk,
                 c.customername
            FROM ORDERS t
            JOIN CUSTOMERS c ON c.customerid = o.ordercustomerfk
        GROUP BY t.ordercustomerfk, MONTH(t.orderdate), YEAR(t.orderdate)) x ON x.order_month = o.order_month
                                                                            AND x.order_year = o.order_year
ORDER BY o.order_year DESC, o.order_month DESC