|
我正在创建一个SQL该句子将按月返回销售摘要。! k; d$ O/ a$ {3 v
为显示日期、总销期、总销售额和总销售价值的简单列。
, D' c! z& l) w: k8 `6 M# E但是,除了这些列,我还需要增加三个,根据成本金额列出最佳客户月份。对于这些列,我需要一些内联子查询,可以返回到它们ID,名称和金额。
: A: u7 p! ]- P5 E我目前的工作是使用内联网SELECT然而,根据我对如何实现这些句子的理解,每个内联句子只能返回一列和一行。
! p) }2 Y3 T% a) f: t为了解决我的情况,我当然可以创建3个单独的内联语句,但是,除了这似乎不切实际之外,它还会使查询时间增加所需要的时间。
8 l8 ]( m/ V' L7 W1 JSELECT 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 CustomerNameFROM Orders GROUP BY DATE_FORMAT(OrderDate,'%m%y')ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC我如何更好地构建这个查询?/ V R) t# I" E Y% t, T" a- ~
完整答案在对Dave Barkers经过一些调整,我为未来寻求帮助的人提供了最终版本。
- K# [* V. A' y戴夫·巴克(Dave Barker)解决方案与客户的详细信息完美配合,但让总销售额和总销售额列出更简单的数字却有些疯狂。
: g* P# j$ @9 D0 n8 tSELECT 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 = 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 = GROUP BY DATE_FORMAT(OrdDate,'%m%y'),OrdCustFK ORDER BY SUM(OrdGrandTotal) DESC) Z ON Z.CustMonth = Y.OrderMonthGROUP BY DATE_FORMAT(OrdDate,'%Y%m')ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC
: g( i w& A; n 解决方案: - E- F2 X; k2 \- O
将内联SQL移动内部联接查询。所以你会有类似的…
0 O J4 C, u( w' `* HSELECT 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') DESC |
|