mysql - 查询以外键和按日期分组的 4 个表

Question

我试图加入 4 张桌子。我将收到显示每个月注册的销售和索赔的产品列表的方式。有4种组合需要考虑：

1. 产品已在某个月份售出，我们已索赔。
2. 产品在某个月份没有售出，我们对此有索赔。
3. 产品已在某个月份售出，我们没有索赔。

4. 产品在某个月份没有售出，我们没有索赔。

   我开始认为创建日期表可能是必要的。

select
   ps.date,
   cl.date,
   ps.part_number,
   ps.sales_quantity,
   cl.claims_quantity
from (select convert(varchar(7), s.date, 120) as date, p.part_number, sum(id.quantity) as sales_quantity
   from parts as p
   left outer join invoice_details as id
   on p.part_number = id.part_number
   left outer join sales as s
   on id.invoice = s.invoice
   group by convert(varchar(7), s.date, 120), p.part_number
   ) ps join 
   (select convert(varchar(7), c.date_registered, 120) as date, p.part_number, sum(c.quantity) as claims_quantity
   from parts as p
   left outer join claims as c
   on p.part_number = c.part_number
   group by convert(varchar(7), c.date_registered, 120), p.part_number
   ) cl
   on ps.part_number = cl.part_number
group by ps.date, cl.date, ps.part_number, ps.sales_quantity, cl.claims_quantity
order by cl.claims_quantity desc

标签： mysqlsql

Answer 1

如果我理解正确，您希望每个部分和每个月都有一行。如果是这样，您需要某种生成月份的方法——因为它们可能不在数据中。

然后，使用 aleft join引入数据和聚合以获取您想要的信息：

select p.part_number, m.mon,
       sum(sales_quantity), sum(claim_quantity)
from parts p cross join
     (select date('2020-01-01') as mon union all
      select date('2020-02-01') as mon 
     ) m left join
     ((select s.date, id.part_number, id.quantity as sale_quantity, null as claim_quantity
       from sales s join
            invoice_details id
            on id.id_sale = s.id_sale
      ) union all
      (select c.date_registered, null, null, quantity
       from claims
      )
     ) sc
     on sc.part_number = p.part_number and
        sc.date >= m.mon and
        sc.date < m.mon + interval 1 month
group by p.part_number, m.mon