MySQL，比較，並非所有記錄都通用的表，在 2 個時間間隔內

我想比較2個時間間隔（2017-10-1到2017-10-31）和（2018-10-1到2018-10-31）之間所有產品銷售的銷量、營業額和利潤率。

例如：

Product Name | Volume 2017 | Volume 2018 | Margin 2017 | Margin 2018 |
    A       |     200     |     100     |     10      |      12     |
    B       |     190     |     120     |     23      |      20     |
    C       |  'no sales' |     100     |  'no sales' |      12     |
    D       |     300     |  'no sales' |     25      |  'no sales' |

我的方法如下：

select
   /* attempt to get either name if null but doesnt work as I join product ID's*/
   if(ifnull(q1.ProductName,false), q2.ProductName , q1.ProductName) as 'Name'
   ,q1.NumofProductsSold as 'Volume 2017'
   ,q2.NumofProductsSold as 'Volume 2018'

   ,format(q1.Margin,2) as 'Margin 2017'
   ,format(q2.Margin,2) as 'Margin 2018'

   ,q1.TotalTurnOver as 'Turnover 2017'
   ,q2.TotalTurnOver as 'Turnover 2018'


from

(   select 

   format(sum(oi.product_quantity),0) as 'NumofProductsSold'
   ,format(sum(oi.turnover),2) as 'TotalTurnOver',
   ,SUM(oi.total_Mark_up) + o_inf.Markup 'Margin'
   ,order_id
    ,ifnull(p.name, 'empty') as 'ProductName'
    ,p.id as 'PID'
    ,op.date as 'date'

    FROM order_items oi JOIN products p ON p.id = op.product_id
                       join product_categories pc on p.id = pc.product_id 
                       join orders o on op.order_id = o.id
                       join order_info o_inf on o.order_number = o_all.orderNumber

    where pc.category_id = 1 -- cat A products only
        AND op.order_status != 'CNL' -- not canceled
          AND op.date >= '2017-09-01' AND op.date <= '2017-10-31'

    group by p.id) as q1

left join 

(  Select

           /* same query BUT for 2018 date range */

) as q2

on q1.PID = q2.PID  -- join on product ID

我的問題是，如果產品在第一個或第二個時間間隔中的任何一個/兩個都沒有任何銷售，我只會得到兩個對應的行，所以如果它們在一個時間間隔內有銷售，我總是“錯過”一些產品而不是另一個。

使用左/右連接，只有當任一集合分別是另一個集合的子集時，我才能獲得正確的結果，即如果產品 ID 出現在右側而不是左側，則它不會出現在左側連接中。我嘗試了 UNION 它可以工作，但它不會產生上述描述中指定的結果。是否有基於我如何設計查詢的解決方法，或者我應該採用不同的方法？

歡迎所有建議-更正-批評！謝謝！

算法：

SELECT product_name,
      COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                        THEN volume END), 'no sale') AS volume2017,
      COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                        THEN volume END), 'no sale') AS volume2018,
      COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                        THEN margin END), 'no sale') AS margin2017,
      COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                        THEN margin END), 'no sale') AS margin 2018
FROM datatable
GROUP BY product_name
/* WHERE `date` BETWEEN '2017-10-1' AND '2017-10-31'
     OR `date` BETWEEN '2018-10-1' AND '2018-10-31' */

引用自：https://dba.stackexchange.com/questions/222078