我有三个表:
- 表:用户/列:user_id、电子邮件、用户名
- 表:活动/列:对象、类型、like_count
- 表: 活动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
+在该表(活动2)中插入的包含"like_"字符串的喜欢数,除此之外,我想加入与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
活动2 表数据:
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)
请考虑使用派生表联接聚合查询,然后在外部查询中运行加法计算。此外,下面的对象列将从第二个聚合查询的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