MySQL:从内联子查询返回多个列

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (20)

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

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

但是,除了这些专栏之外,我还想增加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

如何更好地构造这个查询?


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移动为内部联接查询。

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
用户回答回答于

你也可以做一些类似的事情:

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`

扫码关注云+社区