mysql - 如何在同一个表中添加两行并减去一行？

Question

嗨，我有如下的平板测试

viewtime   event   id
----------------------
10           end   101
290         prog   101    
20           rew   101

我想要像 end+prog-rew 这样的输出，即 (10+290-20)

输出将是:

viewtime   event   id
280         prog   101 

Answer 1

一种解决方案是使用带有 SUM 和 CASE WHEN 的 GROUP BY 查询:

SELECT
  id,
  SUM(CASE WHEN event IN ('end', 'prog') THEN viewtime
           WHEN event = 'rew' THEN -viewtime END) AS viewtime
FROM tablename
GROUP BY id