mysql - 连接表上的多个查询并获取单个选择结果

Question

我有一个表格，其中包含成本中心、供应商、工具、成本和季度列。我想获得两个季度(例如 15/16 Q3 和 15/16 Q2)之间的成本差异结果:

我已经创建了查询来获取 Costcenter 和 Vendor 的结果，如下所示，但它没有适本地连接两个:

select a.Costcenter,c.Vendor,(SumA-SumB) as costcenter_diff, (SumC-SumD) as vendor_diff from
    (select Costcenter ,sum(CostTotal) as SumA
                  from Rawdata
                  where Quarter='15/16 Q3'
                  group by Costcenter)as a,
                  (select Costcenter,sum(CostTotal) as SumB
                  from Rawdata
                  where Quarter='15/16 Q2'
                  group by Costcenter) as b
     Join 
    (select Costcenter,Vendor,sum(CostTotal) as SumC
                  from Rawdata
                  where Quarter='15/16 Q3'
                  group by Costcenter,Vendor)as c,
                  (select Costcenter,Vendor,sum(CostTotal) as SumD
                  from Rawdata
                  where Quarter='15/16 Q2' 
                  group by Costcenter,Vendor) as d where a.Costcenter = b.Costcenter and  
                  c.Costcenter= d.Costcenter and c.Vendor= d.Vendor;

Answer 1

首先，使用条件聚合。例如:

select CostCenter,
       sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
       sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
from rawdata rd
where quarter in ('15/16 Q3', '15/16 Q2');

然后，为供应商执行此操作并将他们联合起来:

select ccv.costcenter, ccv.vendor,
       (cc.sumq3 - cc.sumq2) as cc_diff,
       (ccv.sumq3 - ccv.sumq2) as ccv_diff
from (select CostCenter,
             sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
             sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
      from rawdata rd
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by CostCenter
     ) cc join
     (select CostCenter, vendor,
             sum(case when quarter = '15/16 Q2' then costtotal else 0 end) as sumq2,
             sum(case when quarter = '15/16 Q3' then costtotal else 0 end) as sumq3
      from rawdata rd
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by CostCenter, vendor
     ) ccv
     on cc.costcenter = ccv.costcenter;

注释:

• 为列命名合理的名称。 a 和 b 只是不便于理解查询。
• JOIN 条件应位于 ON 子句中，而不是 WHERE 子句中。
• 切勿在 FROM 子句中使用逗号。 始终使用显式JOIN语法。