这是我的表的结构:
-- users
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | Peter |
| 3 | Ali |
+----+--------+
-- reputations
+----+-------------+---------+-------+------------+------------+
| id | post_id | user_id | score | reputation | date_time |
+----+-------------+---------+-------+------------+------------+ -- Suppose:
| 1 | 1 | 1 | 1 | 5 | 1500489844 | -- out of last week
| 2 | 4 | 3 | -1 | -2 | 1500499815 | -- out of last week
| 3 | 2 | 3 | 1 | 5 | 1500584821 |
| 4 | 3 | 1 | 1 | 5 | 1501389166 |
| 5 | 2 | 4 | 1 | 5 | 1501399142 |
| 6 | 2 | 1 | -1 | -2 | 1501399142 |
| 7 | 4 | 1 | 0 | 15 | 1501481186 |
+----+-------------+---------+-------+------------+------------+
-- Note: the last row came from an accepted-answer, that's why its score is 0
-- post_tag
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 2 |
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
| 3 | 4 |
| 4 | 3 |
+---------+--------+
-- tags
+----+--------+
| id | name |
+----+--------+
| 1 | php |
| 2 | html |
| 3 | css |
| 4 | mysql |
+----+--------+
-- post_category
+---------+-------------+
| post_id | category_id |
+---------+-------------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+---------+-------------+
-- there is an unique index on post_id column. since each post can has only one category
-- categories
+----+------------+
| id | name |
+----+------------+
| 1 | social |
| 2 | technology |
| 3 | political |
+----+------------+
现在我想获得上周用户的声誉、分数、前两个标签的列表。我怎样才能做到这一点?
注1: “top tow tags”是指用户在其中获得较多回购的两个标签。
注 2:每个问题至少有 1 个标签。
这是预期的输出:
+----+--------+-------+------------+----------+------------+
| id | name | score | reputation | tags | category |
+----+--------+-------+------------+----------+------------+
| 1 | Jack | 0 | 18 | css,php | technology |
| 3 | Ali | 1 | 5 | html | political |
| 2 | Peter | 0 | 0 | NULL | NULL |
+----+--------+-------+------------+----------+------------+
-- Note: It's ordered by reputation, score columns
这是我试过的:
SELECT u.*,
sum(r.score) as score,
sum(r.reputation) as reputation
/* WS_CONCAT(',', t.name) as tags */
FROM users u
LEFT JOIN reputation r ON r.user_id = u.id
/* I need more joins to get tags, I don't know how exactly */
WHERE r.date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)
GROUP BY u.id
ORDER BY reputation, score
如您所见,我的查询缺少最后一列(tags)。老实说,我不知道我该如何处理这样的事情。你知道如何?
对于 MySQL(PostgreSQL 和其他数据库)来说,这是一项棘手的工作,可以更轻松地处理它。
为清楚起见,我已将您的
tag.name
totag.tag
和user.name
to更改为,以user.user_name
减少混淆name
s 的风险。另请注意,您的数据包含一个 user4
。我叫他詹姆斯。让我们一步一步来。
步骤1
首先,让我们得到减去标签的预期输出:
注意:条件
r.date_time > 1500584821
必须是您的JOIN ON
条件的一部分。如果它在where
条款中,你LEFT JOIN
就变成了一个INNER JOIN
,你将失去没有声誉的用户。第2步
现在我们进行另一个查询,为每个 user_id 返回标签列表及其相应的声誉:
步骤 3
我们
LEFT JOIN
将两者放在一起(在对第二个进行稍微简化之后,忽略那里的用户名),ON user_id
并且GROUP BY user_id, user_name, score, reputation
请注意,
group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR ',')
它会返回与用户关联的所有标签。我们通过 削减到两个substring_index(string,separator,number)
。您可以在dbfiddle此处找到所有设置和查询
旁注(排名时的关系):对于用户
Jack
,php
和mysql
标签具有相同的tag_reputation
。即:第二名和第三名之间有平局。在这种情况下,如果没有第二个标签排序标准,则css,mysql
和css,php
都是“前两个”标签的可接受答案。