mysql - mySQL 中每个子查询的最大值

Question

我正在使用下面提到的查询来获取成本中心、供应商和工具两个季度之间 > 30 的差异和百分比变化。 Costcenter、供应商和工具的输出类似于:

我想要得到一个结果，该结果将为成本中心提供最大差异，然后是该成本中心的最大供应商和该供应商的最大工具，例如对于成本中心 A，供应商 c 是最大值，对于供应商 c 最大工具是 4。

我正在使用的查询是:

select ccv.Costcenter,
       (cc.sumq3 - cc.sumq2) as costcenter_diff, ((((cc.sumq3 - cc.sumq2)/cc.sumq2)*100) ) as Costcenter_change, ccv.Vendor,
       (ccv.sumq3 - ccv.sumq2) as venor_diff, ((((ccv.sumq3 - ccv.sumq2)/ccv.sumq2)*100)) as Vendor_change, ccvt.Tool,(ccvt.sumq3 - ccvt.sumq2) as Tool_diff,((((ccvt.sumq3 - ccvt.sumq2)/ccvt.sumq2)*100)) as Tool_Change
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 Glm_Test.CostCenter_Rawdata
      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 Glm_Test.CostCenter_Rawdata
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by Costcenter, Vendor
     ) ccv join
     (select Costcenter, Vendor,Tool,
             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 Glm_Test.CostCenter_Rawdata
      where quarter in ('15/16 Q3', '15/16 Q2')
      group by Costcenter, Vendor,Tool
     ) ccvt
     on cc.Costcenter = ccv.Costcenter and ccv.Vendor=ccvt.Vendor and cc.Costcenter = ccvt.Costcenter
    Having  Costcenter_change > 30  and Vendor_change > 30 and tool_change >30  ;

Answer 1

Vendor 是 max 是什么意思？他的成本中心销售额最高？

我建议您使用分区来查找最大值，您也可以使用它而不需要将数据连接三​​次。

例如使用:

RANK() OVER (PARTITION BY Costcenter ORDER BY sumq3-sumq2)

对于成本中心，其中 sumq3 和 sumq2 将是脚本中案例的总和。

RANK() OVER (PARTITION BY Costcenter,Vendor ORDER BY sumq3-sumq2)

对于供应商等

然后从该输出中仅选择所有三个等级均为 1 的位置。

您还需要分别对 sumq3 和 sumq2 进行列操作，以了解分区结果。

希望对您有所帮助。

编辑:试试这个:

with costs as
(     
select Costcenter,
Vendor,
Tool,
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 Glm_Test.CostCenter_Rawdata
where quarter in ('15/16 Q3', '15/16 Q2')
group by Costcenter,Vendor,Tool
)
select Costcenter,sum(sumq3)-sum(sumq2) over (partition by Costcenter)     as Costcenter_diff
Vendor,sum(sumq3)-sum(sumq2) over (partition by Costcenter,Vendor) as     Vendor_diff,
Tool,sum(sumq3)-sum(sumq2) over (partition by Costcenter,Vendor,Tool) as     Tool_diff
   from costs