mysql 如何计算每个分组中 最大版本记录 的每一列的和

Question

SELECT
    dept_id,
    version,
    manage_three,
    manage_four,
    manage_five,
    manage_six,
    manage_seven,
    manage_eight,
    manage_nine,
    manage_ten
FROM
    (select * from ahrs_post_plan order by version desc) A
WHERE
    dept_id = 29 
    OR dept_id IN ( SELECT dept_id FROM sys_dept WHERE FIND_IN_SET( '29', ancestors ) ) 
    AND STATUS = '0' 

---

这是未分组的，有不同版本的数据

---

这是分组后的

问题是我使用了sum(),它把所有记录都加了，我只想计算分组后的和

Answer 1

描述的不是很清楚，也没说按什么分组，只能通过截图和标题猜测大概这样，另该语法不一定支持（sql_mode）

select t1.dept_id,(t2.manage_three+t2.manage_four+t2.manage_five+t2.manage_six+t2.manage_seven+t2.manage_eight+t2.manage_nine+t2.manage_ten) total
 from (SELECT dept_id,max(version) version
    FROM ahrs_post_plan
    WHERE dept_id = 29 OR dept_id IN ( SELECT dept_id FROM sys_dept WHERE FIND_IN_SET( '29', ancestors ) ) AND STATUS = '0'
    group by dept_id
   )t1 inner join ahrs_post_plan t2 on t1.dept_id=t2.dept_id and t1.version=t2.version
group by dept_id