MySQL查询：筛选成本高于平均值的合同ID

完整查询语句 #

SELECT contract_id
FROM (
    SELECT 
        contracts.contract_id,
        SUM(contracts.hours * staff.rate) AS contract_cost
    FROM contracts
    INNER JOIN staff ON contracts.staff_id = staff.staff_id
    GROUP BY contracts.contract_id
) AS contract_costs
WHERE contract_cost > (
    SELECT AVG(average_cost) 
    FROM ( 
        SELECT SUM(contracts.hours*staff.rate) AS average_cost 
        FROM contracts 
        INNER JOIN staff ON contracts.staff_id = staff.staff_id 
        GROUP BY contracts.contract_id 
    ) AS inner_avg
);

逻辑拆解 #

1. 计算单个合同总成本：内层的contract_costs子查询通过关联contracts和staff表，用SUM(contracts.hours * staff.rate)计算每个合同的总成本，同时保留对应的contract_id。
2. 计算所有合同的平均成本：嵌套的inner_avg子查询先算出每个合同的成本，再用AVG()函数得到整体平均值（也就是你提到的约1344.167）。
3. 筛选符合条件的合同：最后在外层查询中，筛选出总成本高于平均值的合同ID，得到你需要的结果。

示例数据验证 #

根据你提供的示例数据，我们可以手动计算每个合同的成本：

• 合同55：30小时 × 50费率 = 1500
• 合同45：25小时 × 45费率 = 1125
• 合同43：30小时 × 80费率 = 2400
• 合同41：12小时 × 45费率 = 540
• 合同67：20小时 × 80费率 = 1600
• 合同49：20小时 × 45费率 = 900

所有合同的平均成本为 (1500+1125+2400+540+1600+900)/6 ≈ 1344.167，因此成本高于该值的合同是55、43、67，和你期望的结果完全一致。

内容的提问来源于stack exchange，提问作者sontoshine