mysql多个子查询_MySQL:从一个内嵌子查询返回多个列

我正在创建一个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

如何更好地构造此查询?

完整答案

在对DaveBarkers的解决方案进行了一些调整之后,我为将来寻求帮助的任何人准备了一个最终版本。

DaveBarker的解决方案与客户的详细信息完美结合,但是它使简单的总销售额和总销售额栏得到了一些疯狂的数字。

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