mysql - mysql如何合并列的值

Question

让我们考虑这个查询

select class_id,case when event_id=2 then sum(time_spent) end as timespent ,case when event_id=3 then sum(timespent) end as visitedtimespent from class group by class_id,event_id;

输出看起来像

class_id     timespent visitedtimespent

1             2000       NULL
1             NULL        10
2             4000       NULL
2             NULL        5

当我使用此查询时

select class_id,case when event_id=2 then sum(time_spent) end as timespent ,case when event_id=3 then sum(time_spent) end as timespent from class group by class_id;

输出看起来像

class_id     timespent visitedtimespent

1             2000       NULL
2             4000       NULL

但我期望这个输出

class_id     timespent visitedtimespent

1             2000        10
2             4000         5

我怎样才能实现这个目标？

Answer 1

select class_id,
       sum(case when event_id=2 then time_spent else 0 end) as timespent, 
       sum(case when event_id=3 then time_spent else 0 end) as visitedtimespent
from class 
group by class_id