mysql - 列匹配的累计和

我想进行查询，以便为我提供所有星期的累计金额，但按月牙顺序显示

Person | Week 1 | Week2 = Week1 + Week2 | Week 3 = Week 1 + 2 + 3|

我的桌子就像

ID | Week | Person | Item 1 | Item 2 | Item 3 | Iteration

一个星期的结果是所有项目的总和，并且迭代应该是该星期的最高数目。

到目前为止，我已经解决了这个问题，但是结果不是预期的，因为对于所有行，week1的总和都相等，这是错误的

SELECT person, w1sum, w2sum
FROM table AS t1, (

SELECT SUM( item1 + item2 + item3 ) AS w1sum
FROM table
WHERE week =1
AND iteration
IN (

SELECT MAX( iteration )
FROM table
)
    GROUP BY person

) AS week1,
(

SELECT SUM( item1 + item2 + item3 ) AS w2sum
FROM table
WHERE week = 1 AND week = 2
AND iteration
IN (

SELECT MAX( iteration )
FROM table
)
    GROUP BY person

) AS week2

GROUP BY person

最佳答案

我认为您可以使用条件聚合来完成您想做的事情：

select person,
       sum(case when week in (1) then item1 + item2 + item3 else 0 end) as week1,
       sum(case when week in (1, 2) then item1 + item2 + item3 else 0 end) as week2
       sum(case when week in (1, 2, 3) then item1 + item2 + item3 else 0 end) as week3
from table t
where not exists (select 1
                  from table t2
                  where t2.person = t.person and
                        t2.week = t.week and
                        t2.iteration > t.iteration
                 )
group by person;

where子句获取每个人每个星期的最后一次迭代。

关于mysql - 列匹配的累计和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21987877/