如何基于ID从表列总和扣除金额后均分至对应行

原表数据 #

你的原查询直接将SUM(deposit_amount)-50的结果赋值给每一行，这会导致所有行都被设置为同一个固定值，无法实现均分效果。要达成需求，需要先计算目标分组的总金额与记录数，再用（总金额-扣除额）除以记录数得到每行应更新的数值。

方案一：关联子查询写法 #

直接在UPDATE语句中嵌套子查询计算总金额和记录数：

query = em.createNativeQuery("""
    UPDATE deposit d
    SET deposit_amount = (
        (SELECT SUM(deposit_amount) FROM deposit WHERE comp_id = :comp_id) - 50
    ) / (SELECT COUNT(*) FROM deposit WHERE comp_id = :comp_id)
    WHERE d.comp_id = :comp_id
""");
query.setParameter("comp_id", comp_id);

方案二：CTE优化查询效率 #

如果目标分组数据量较大，使用CTE可以避免重复扫描表，提升执行效率：

query = em.createNativeQuery("""
    WITH comp_stats AS (
        SELECT 
            SUM(deposit_amount) AS total_amount,
            COUNT(*) AS record_count
        FROM deposit 
        WHERE comp_id = :comp_id
    )
    UPDATE deposit d
    SET deposit_amount = (cs.total_amount - 50) / cs.record_count
    FROM comp_stats cs
    WHERE d.comp_id = :comp_id
""");
query.setParameter("comp_id", comp_id);

补充说明 #

• 两种方案都会计算comp_id对应的总金额，减去指定扣除额后，均分至该分组的每一行（如示例中300-50=250，250/3≈83.3）。
• 若需要控制小数位数，可使用ROUND()函数，例如ROUND((cs.total_amount - 50)/cs.record_count, 1)来保留一位小数。

期望执行结果 #

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