MySQL Statement (Group and SUM() => getting multiple "sum"result per line

Question

我有几个表:

table user:
id  firstname   lastname
1   John      Doe
2   Jane      Skith
3   will      Smith
...

table member:
member_id    member_user_id member_status member_activated_by
10         1              yes           partner 1
11         1              yes           partner 2
12         1              yes           partner 3
13         2              yes           partner 2
14         3              no            ----
...

table points:
points_id    points_user_id points_value points_date
10         1              10           2012-02-15 
11         2              15           2012-02-15 
12         2              20           2012-02-15 
13         1              5            2012-02-15 
14         3              30           2012-02-15 
15         1              12           2012-02-15 
...

So results SHOULD be:
 id1 - John Doe:   27 Points
 id2 - Jane Skith: 35 Points
 id3 - will Smith: nothing (not activated)

问题是，id1 带来了 81 点(因为他已经被激活了 3 次......)

这是我当前的 mysql 字符串(缩短...)

SELECT points_user_id, SUM( points_value ) AS points_total, id, firstname, lastname
FROM user
JOIN member ON member.member_user_id = user.id
JOIN points ON points.points_user_id = user.id
WHERE 1
AND member_status = 'yes'
GROUP BY points_user_id
ORDER BY points_total DESC 
LIMIT 0 , 100

.. 到目前为止，它“几乎”按预期工作。

但是:用户可能有几个合作伙伴将他激活“进入激活”表。

现在调用它时，我得到了 points_total 的重复(它将被计算多次，因为 uesr 已被合作伙伴激活......)这在排名列表中没有多大意义......

如何为每个 user_id 获得“一个”points_total

希望你明白，我想要达到的目标......非常感谢!

Answer 1

我认为这应该可行，而不是加入激活进行子选择，您将在其中每个 user_id 只获得一行然后加入这个子选择，我不确定这是否是 MySql 语法，但它应该向您展示方法。唯一要注意的是，您在内部查询中选择的字段对于相同的 user_id 可能不会更改

SELECT user_id, SUM( point_row ) AS points_total, user_data...,...
    FROM points
    JOIN user_data AS UD
    ON UD.user_id = points.user_id

JOIN ( SELECT field1,field2,field3, MAX(activation.user_id) as user_id 
  FROM activation WHERE activation_info = 'yes'
  GROUP BY field1,field2,field3 AS ACTIV )  AS ACTIV
  ON user_data.user_id = AS ACTIV.user_id

WHERE points.points_status = '1'
AND   ACTIV.activation_info = 'yes'
GROUP BY points.user_id
ORDER BY total DESC 
LIMIT 0 , 100