php - 如何从同一个表中选择不同的总和值

Question

我有两个表:

用户

userid name surname active

事件

userid activity type

有 3 种类型的事件。

我想根据他们的类型选择所有用户的总事件数。结果集应显示如下:

userid name surname at1 at2 at3

at1 代表事件类型 1 的总事件计数(at2 和 at3 相同)。

我已尝试使用该查询但返回 null。

SELECT u.userid, u.name, u.surname, 
       SUM( a1.activity ) AS at1,
       SUM( a2.activity ) AS at2, 
       SUM( a3.activity ) AS at3
FROM users AS u
INNER JOIN activities AS a1 ON a1.userid = u.userid
INNER JOIN activities AS a2 ON a2.userid = u.userid
INNER JOIN activities AS a3 ON a3.userid = u.userid
WHERE u.active=1
      AND a1.type =1
      AND a2.type =2
      AND a3.type =3

Answer 1

你可以这样做(这是伪代码):

select u.name, (SELECT sum(a1.activity) from activities a1 where a1.type=1 and
a1.userid=u.userid) as at1, (SELECT sum(a2.activity) from activities a2 where a2.type=2 and
a2.userid=u.userid) as at2 FROM user AS u group by u.userid