mysql - 计算转发的推文数量和已转发的推文总数

Question

考虑下表

users                                tweets
---------------------------------    ---------------------------
user_id num_retweets sum_retweets    tweet_id user_id retweeted
---------------------------------    ---------------------------
1                                    1        1       3
2                                    2        1       0
3                                    3        1       4
                                     4        2       0
                                     5        2       0
                                     6        3       1
                                     7        3       2
                                     8        3       0

我想统计num_retweets:用户撰写已转发推文的次数和sum_retweets:用户所有推文被转发的次数被转发。 UPDATE 查询后预期的 users 表为:

users
---------------------------------
user_id num_retweets sum_retweets
---------------------------------
1       2            7 <-- 3 + 4
2       0            0
3       2            3 <-- 1 + 2

对于构建这两个查询的任何帮助，我们将不胜感激:-)我一直在跨表执行UPDATE时遇到问题。

Answer 1

UPDATE
USERS u
JOIN (
SELECT 
    tweets.user_id,
    COUNT(IF(tweets.retweeted > 0, 1, null)) as num_retweets,
    SUM(tweets.retweeted) as sum_retweets
FROM tweets
GROUP BY tweets.user_id
) as t ON t.user_id = u.user_id
SET u.num_retweets = t.num_retweets, u.sum_retweets = t.sum_retweets