我有3个表，客户，product_type_1和product_type_2之一。

顾客

| ID |名称|
| ---- | ------- |
| 1 |亚历克斯|
| 2 |约翰|
| 3 |艾哈迈德|
| 4 |山姆|

product_type_1

| ID | order_by |日期
| ---- | -------------- | ------- |
| 1 | ------ 1个---- | 2019-03-01
| 2 | ------ 2 ---- | 2019-03-02
| 3 | ------ 2 ---- | 2019-03-03
| 4 | ------ 3 ---- | 2019-03-04

product_type_2

| ID | order_by |日期
| ---- | -------------- | ------- |
| 1 | ------ 1个---- | 2019-03-01
| 2 | ------ 3 ---- | 2019-03-02
| 3 | ------ 3 ---- | 2019-03-03
| 4 | ------ 2 ---- | 2019-03-04

最终输出将是一个特定的一年中每个月的客户名称分组都product型的量的总和。我已经写了查询，但它同时适用于1种product类型。但我想双方即总和：

客户|一月|二月|三月....总
：------------------------------------------------------：
约翰------ | 0 -- | --- 0 | --- 3 ...... 3

正如约翰订购共有3种product在2019年。

该查询

select c.name,
               sum( month(o.order_date) = 1 and year(o.order_date)=2010) as Jan,
               sum( month(o.order_date) = 2 and year(o.order_date)=2010) as Feb,
               sum( month(o.order_date) = 3 and year(o.order_date)=2010) as Mar,
               sum( month(o.order_date) = 4 and year(o.order_date)=2010) as Apr,
               sum( month(o.order_date) = 5 and year(o.order_date)=2010) as May,
               sum( month(o.order_date) = 6 and year(o.order_date)=2010) as Jun,
               sum( month(o.order_date) = 7 and year(o.order_date)=2010) as Jul,
               sum( month(o.order_date) = 8 and year(o.order_date)=2010) as Aug,
               sum( month(o.order_date) = 9 and year(o.order_date)=2010) as Sep,
               sum( month(o.order_date) = 10 and year(o.order_date)=2010) as Oct,
               sum( month(o.order_date) = 11 and year(o.order_date)=2010) as Nov,
               sum( month(o.order_date) = 12 and year(o.order_date)=2010) as December,
               count(*) as total
        from customers c join
          (
          select order_by as cID, order_price , order_date
             from orders where year(order_date)=2010
          ) o
        on o.cID = c.id and o.order_price > 0
        group by c.name
        order by total desc
分析解答

使用union all和聚集：

select c.id, c.name,
       sum( month(o.order_date) = 1 and year(o.order_date)=2010) as Jan,
       . . .
from customers c left join
     ((select order_by, date
       from product_type_1
      ) union all
      (select order_by, date
       from product_type_2
      ) 
     ) p12
     on p12.order_by = c.id
group by c.id, c.name