mysql - 将两个复杂的 SQL 查询合并为一个

Question

我有 3 个表，其中一个是客户的，product_type_1 和 product_type_2。

客户

| id | name  |
|----|-------|
| 1  | Alex  |
| 2  | John  |
| 3  | Ahmad |
| 4  | Sam   |

产品类型_1

| id | order_by  | Date
|----|--------------|-------|
| 1  |------ 1 ---- | 2019-03-01
| 2  |------ 2 ----| 2019-03-02
| 3  |------ 2 ----| 2019-03-03
| 4  |------ 3  ----| 2019-03-04

产品类型_2

| id | order_by  | Date
|----|--------------|-------|
| 1  |------ 1 ---- | 2019-03-01
| 2  |------ 3 ----| 2019-03-02
| 3  |------ 3 ----| 2019-03-03
| 4  |------ 2  ----| 2019-03-04

最终输出将是特定年份每个月按客户名称分组的两种产品类型的金额总和。我已经编写了查询，但它一次适用于 1 种产品类型。但我想要两者的总和，即:

Customer | Jan | Feb | Mar .... Total<br>
:------------------------------------------------------:
John  ------   |  0  -- |--- 0   |--- 3  ......  3

因为约翰在 2019 年总共订购了 3 件产品。

查询是

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

Answer 1

使用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