mysql多次查询实现同比比较

Question

我正在尝试从查询中获得逐年比较。我有这个疑问...

SELECT
CEIL(AVG(o.CustomerOrderTotal)) AS Av2013,
SUM(o.CustomerOrderTotal) AS TOTAL2013,
COUNT(o.Order_ID) AS NumOrders2013,
o.Customer_ID,
MAX(o.OrderPlaceServerTime) AS LastOrder,
cg.Category,
rg.RegionName
FROM Orders o
LEFT JOIN CustomerDetails cd ON cd.Customer_ID = o.Customer_ID
LEFT JOIN _CustomerCategory cg ON cg.Category_ID = cd.Category_ID
LEFT JOIN _RegionsNew rg ON rg.Region_ID = cd.Region_ID
WHERE o.OrderPlaceServerTime >= '2013-01-01 00:00:00'
AND o.OrderPlaceServerTime <= '2013-01-31 23:59:59'
AND o.CustomerOrderTotal > 0
AND o.IsVOID = 0
GROUP BY o.Customer_ID

这个查询可以正常工作一年，我得到

|Customer_ID|LastOrder|Category|RegionName|Av2013|TOTAL2013|NumOrders2013|

我的问题是，添加另一年的最佳方法是什么，例如将 2014 添加到查询中并获得结果...

|Customer_ID|LastOrder|Category|RegionName|Av2013|TOTAL2013|NumOrders2013|Av2014|TOTAL2014|NumOrders2014|

有人能指出我正确的方向吗？

::编辑::

好吧，如果我只想要一个月，或者连续几个月呢？

这样的东西有用吗？

    SELECT o.Customer_ID,
       cg.Category,
       rg.RegionName,
       MAX(o.OrderPlaceServerTime) AS LastOrder,

       CEIL(AVG(case when year(OrderPlaceServerTime) = 2013 then o.CustomerOrderTotal end)) AS Av2013,
       SUM(case when year(OrderPlaceServerTime) = 2013 then o.CustomerOrderTotal end) AS TOTAL2013,
       SUM(case when year(OrderPlaceServerTime) = 2013 then 1 else 0 end) AS NumOrders2013,

       CEIL(AVG(case when year(OrderPlaceServerTime) = 2014 then o.CustomerOrderTotal end)) AS Av2014,
       SUM(case when year(OrderPlaceServerTime) = 2014 then o.CustomerOrderTotal end) AS TOTAL2014,
       SUM(case when year(OrderPlaceServerTime) = 2014 then 1 else 0 end) AS NumOrders2014

FROM Orders o
LEFT JOIN CustomerDetails cd ON cd.Customer_ID = o.Customer_ID
LEFT JOIN _CustomerCategory cg ON cg.Category_ID = cd.Category_ID
LEFT JOIN _RegionsNew rg ON rg.Region_ID = cd.Region_ID 

WHERE ( o.OrderPlaceServerTime >= '2013-01-01 00:00:00' and o.OrderPlaceServerTime <= '2013-01-31 23:59:59' 
OR  o.OrderPlaceServerTime >= '2014-01-01 00:00:00' and o.OrderPlaceServerTime <= '2014-01-31 23:59:59' )
AND o.CustomerOrderTotal > 0
AND o.IsVOID = 0
GROUP BY o.Customer_ID

Answer 1

您需要将条件移至 select 子句:

SELECT o.Customer_ID,
       cg.Category,
       rg.RegionName,
       CEIL(AVG(case when year(OrderPlaceServerTime) = 2013 then o.CustomerOrderTotal)) AS Av2013,
       SUM(case when year(OrderPlaceServerTime) = 2013 then o.CustomerOrderTotal end) AS TOTAL2013,
       SUM(case when year(OrderPlaceServerTime) = 2013 then 1 else 0 end) AS NumOrders2013,
       MAX(case when year(OrderPlaceServerTime) = 2013 then o.OrderPlaceServerTime end) AS LastOrder,
       CEIL(AVG(case when year(OrderPlaceServerTime) = 2014 then o.CustomerOrderTotal)) AS Av2014,
       SUM(case when year(OrderPlaceServerTime) = 2014 then o.CustomerOrderTotal end) AS TOTAL2014,
       SUM(case when year(OrderPlaceServerTime) = 2014 then 1 else 0 end) AS NumOrders2014,
       MAX(case when year(OrderPlaceServerTime) = 2014 then o.OrderPlaceServerTime end) AS LastOrder2014
FROM Orders o LEFT JOIN
     CustomerDetails cd
     ON cd.Customer_ID = o.Customer_ID LEFT JOIN 
     _CustomerCategory c
     ON cg.Category_ID = cd.Category_ID LEFT JOIN
     _RegionsNew rg ON rg.Region_ID = cd.Region_ID
WHERE o.OrderPlaceServerTime >= '2013-01-01 00:00:00' and
      o.OrderPlaceServerTime <= '2014-01-31 23:59:59' and
      o.CustomerOrderTotal > 0 and
      o.IsVOID = 0
GROUP BY o.Customer_ID;