mysql - 对三个表进行计数和求和，获取用户信息及其点赞数

Question

我有三个表:

1. 表:用户/列: user_id、电子邮件、用户名
2. 表格:事件/列:对象、类型、like_count
3. 表:事件2/列:对象、target_type、主题、类型

事件表中的 like_count 没有问题，我用这个查询计算所有这些:

SELECT SUM(activities.like_count) AS likes, users.user_id, users.email, users.username
FROM activities
INNER JOIN users
ON activities.subject = users.user_id
GROUP BY user_id

但事件表中还有另一个计数，每次喜欢其他类型的帖子时都会插入(未更新)，我通过此查询对它们进行了计数:

SELECT activities.subject, activities.object, COUNT(activities.type) AS likes
FROM activities
INNER JOIN activities2
ON activities.object = activities2.object AND activities2.target_type = 'parent'
WHERE activities2.type LIKE 'like_%'
GROUP BY activities2.subject

但是当我想将他们加入在一起时，问题就从这里开始了!我想计算 like_count + 插入包含“like_”字符串的表(activities2)中的点赞数，除此之外，我想加入与 user_id 相关的主题(在事件表中)在另一个表(用户)中。

我的最后一个查询是这样的:

SELECT users.user_id, users.email, users.username, activities.object, COUNT(activities.type)+SUM(activities.like_count) AS likes
FROM activities
INNER JOIN activities2
ON activities.object = activities2.object AND activities2.target_type = 'parent'
INNER JOIN users
ON activities.subject = users.user_id
WHERE activities2.type LIKE 'like_%'
GROUP BY users.user_id

问题是加入时，它获取的用户信息不是我想要的。总之，我想要这样的东西:

user_id------email-----------------username----------object-------likes
2521---------a@b.com---------------abc---------------9578---------3
5484---------c@d.com---------------def---------------8547---------16
8431---------e@f.com---------------ghi---------------4584---------1

当然结果是这样的，但只有喜欢才是正确的，但是用户表中的 user_id、电子邮件、用户名列不是!

我想知道你是否愿意帮忙解决这个问题。我真的厌倦了尝试和面对没有结果:(

用户表数据:

user_id-------------email---------------username
1-------------------a@b.com-------------abc
2-------------------c@d.com-------------def
3-------------------e@f.com-------------ghi

事件表数据:

object----------type----------------like_count------subject
20--------------like_video----------0---------------1
20--------------like_photo----------0---------------2
33--------------like_music----------0---------------3
33--------------some_other_type-----5---------------6
33--------------some_other_type-----8---------------10

activities2表数据:

object------target_type-----subject-----type
20----------parent----------30----------like_video
21----------owner-----------40----------like_audio
22----------parent----------50----------something_not_start_with_like_

我想要:

user_id------email-----------------username----------object-------likes
1------------a@b.com---------------abc---------------9578---------(sum of like_count + count of type which has like_ in first characters)
2------------c@d.com---------------def---------------8547---------(sum of like_count + count of type which has like_ in first characters)
3------------e@f.com---------------ghi---------------4584---------(sum of like_count + count of type which has like_ in first characters)

Answer 1

考虑使用派生表连接聚合查询，然后在外部查询中运行加法计算。此外，下面的 object 列已从第二个聚合查询的 GROUP BY 子句中删除，但仍在 JOIN 中使用，因为您需要在 处求和学科级别。

SELECT u.user_id, u.email, u.username, a.`object`, u.likes + a.likes as `total_likes`
FROM
   (SELECT SUM(activities.like_count) AS likes, users.user_id, users.email, users.username
    FROM activities
    INNER JOIN users
    ON activities.subject = users.user_id
    GROUP BY user_id, users.email, users.username) u

INNER JOIN
   (SELECT activities.subject, COUNT(activities.type) AS  likes
    FROM activities
    INNER JOIN activities2
    ON activities.object = activities2.object AND activities2.target_type = 'parent'
    WHERE activities2.type LIKE 'like_%'
    GROUP BY activities2.subject) As a

ON u.user_id = a.subject