MySQL: Zwracanie wielu kolumn z podzapytania w wierszu
Tworzę instrukcję SQL, która zwróci miesięczne podsumowanie sprzedaży.
Podsumowanie zawiera kilka prostych kolumn na datę, całkowitą liczbę sprzedaży i całkowitą wartość sprzedaży.
Jednak oprócz tych kolumn chciałbym dodać jeszcze 3, które będą wymieniać najlepsze miesiące dla klientów według wydanej kwoty. Dla tych kolumn potrzebuję jakiegoś podkwerendy wbudowanej, która może zwrócić ich identyfikator, nazwę i wydaną kwotę.
Mój obecny wysiłek wykorzystuje inlineSELECT
jednak z mojej wiedzy o tym, jak je wdrożyć, można zwracać tylko jedną kolumnę i wiersz na jedną instrukcję.
Aby obejść to w moim scenariuszu, mogę oczywiście utworzyć 3 oddzielne instrukcje in-line, jednak oprócz tego, że wydaje się to niepraktyczne, zwiększa to czas zapytania bardziej niż to konieczne.
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
Jak mogę lepiej uporządkować to zapytanie?
PEŁNA ODPOWIEDŹPo pewnym ulepszeniu rozwiązania Dave Barkers, mam ostateczną wersję dla każdego w przyszłości szukającego pomocy.
Rozwiązanie Dave'a Barkera doskonale współgrało z danymi klienta, jednak dzięki prostszym kolumnom Total Sales i Total Sale Amount pojawiły się szalone liczby.
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