题解 | #返回产品名称和每一项产品的总订单数#

/*select prod_name,
       orders 
   from (
select prod_name ,
       if(a.orders is not null,a.orders,0) orders 
  from Products P 
  left join 
  (
    select prod_id ,
           count(order_num) orders 
      from OrderItems OI 
      group by OI.prod_id 
  ) a  on P.prod_id = a.prod_id 

  union 
select prod_name ,
       if(a.orders is not null,a.orders,0) orders
  from Products P 
  right join 
  (
    select prod_id ,
           count(order_num) orders 
      from OrderItems OI 
      group by OI.prod_id 
  ) a  on P.prod_id = a.prod_id 
) b order by prod_name ;   
**/  

select prod_name ,
       count(order_num) orders 
       from (
select prod_name,
       order_num 
  from Products P 
  left join OrderItems OI using(prod_id) 
  union 
select prod_name,
       order_num 
  from Products P 
  right join OrderItems OI using(prod_id)  
) a  
group by prod_name 
order by prod_name  ;