从mysql中的最大值求和

Question

我试图从一个表中获得最大值的总和：

select 
a.sp_capex_01_master_key,
a.sp_capex_01_master_wbs_id,
format((
(
select
sum(maxVal)
from 
(
select max(w.sp_capex_01_trans_realisasi) as maxVal 
from sp_capex_01.sp_capex_01_trans w 
where w.sp_capex_01_master_wbs_id='P2-14101-01' 
group by w.sp_capex_01_master_key 
) t
) / b.sp_capex_01_master_wbs_bud * 100
),2) as 'PerBudget'
from sp_capex_01_master a
join sp_capex_01.sp_capex_01_master_wbs b 
on a.sp_capex_01_master_wbs_id=b.sp_capex_01_master_wbs_id;

如果我把值设置为w.sp_capex_01_master_wbs_id='P2-14101-01'，那么我得到的结果是错误的。

但当我将其更改为w.sp_capex_01_master_wbs_id=a.sp_capex_01_master_wbs_id时，我会收到以下错误消息：

Error Code: 1054. Unknown column 'a.sp_capex_01_master_wbs_id' in 'where clause'    0.000 sec

如何获得最大值之和？

Answer 1

使用按要匹配的列分组的子查询进行联接。

SELECT 
a.sp_capex_01_master_key,
a.sp_capex_01_master_wbs_id,
FORMAT(sum_maxval / b.sp_capex_01_master_wbs_bud * 100, 2) AS PerBudget
FROM sp_capex_01_master a
JOIN sp_capex_01.sp_capex_01_master_wbs b ON a.sp_capex_01_master_wbs_id=b.sp_capex_01_master_wbs_id
LEFT JOIN (
SELECT sp_capex_01_master_wbs_id, SUM(maxVal) AS sum_maxval
FROM (
select w.sp_capex_01_master_wbs_id, max(w.sp_capex_01_trans_realisasi) as maxVal 
from sp_capex_01.sp_capex_01_trans w 
group by w.sp_capex_01_master_wbs_id, w.sp_capex_01_master_key
) AS w1
GROUP BY sp_capex_01_master_wbs_id
) AS w2 ON w2.sp_capex_01_master_wbs_id = a.sp_capex_01_master_wbs_id