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回答 3
Stack Overflow用户
回答已采纳
发布于 2009-11-19 12:27:09
将内联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') DESCStack Overflow用户
发布于 2013-12-19 19:00:47
您还可以执行以下操作：
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`Stack Overflow用户
发布于 2009-11-19 13:09:37
试一试：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1760817
复制相关文章
相似问题