MySQL:从内联子查询返回多个列
MySQL:从内联子查询返回多个列
提问于 2009-11-19 12:07:40
我正在创建一条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我怎样才能更好地组织这个查询？
完整答案
在对Dave Barkers解决方案进行了一些调整之后，我有了一个最终版本，供将来寻求帮助的任何人使用。
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1760817
复制相关文章
相似问题