MySQL: Retornando várias colunas de uma subconsulta in-line
Estou criando uma instrução SQL que retornará um resumo mensal sobre vendas.
O resumo listará algumas colunas simples para a data, o número total de vendas e o valor total das vendas.
No entanto, além dessas colunas, gostaria de incluir mais três que listarão os meses como melhor cliente pelo valor gasto. Para essas colunas, preciso de algum tipo de subconsulta in-line que possa retornar seu ID, Nome e a quantia que eles gastaram.
Meu esforço atual usa um inlineSELECT
declaração, no entanto, do meu conhecimento sobre como implementar estes, você só pode retornar uma coluna e linha por instrução in-line.
Para contornar isso com o meu cenário, eu posso, é claro, criar 3 instruções em linha separadas, no entanto, além de parecer impraticável, aumenta o tempo de consulta mais do que o necessário.
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
Como posso estruturar melhor essa consulta?
RESPOSTA COMPLETADepois de alguns ajustes na solução de Dave Barkers, eu tenho uma versão final para qualquer um no futuro procurando por ajuda.
A solução de Dave Barker funcionou perfeitamente com os detalhes do cliente, no entanto, fez com que as colunas mais simples de Vendas Totais e Quantia Total de Venda obtivessem algumas figuras loucas.
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