mysql - 我如何计算帖子数量？

这是我的表格结构：

-- 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 |
| 8  | 5           | 1       | 1     | 5          | 1501481297 |
+----+-------------+---------+-------+------------+------------+
-- 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      |
| 5       | 1      |
+---------+--------+

-- tags
+----+--------+
| id |  name  |
+----+--------+
| 1  | php    |
| 2  | html   |
| 3  | css    |
| 4  | mysql  |
+----+--------+

我的问题是：

SELECT
    t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score
FROM
    users u
    LEFT JOIN reputations r
        ON r.user_id = u.id
            AND r.date_time > 1500584821
    JOIN post_tag pt ON pt.post_id = r.post_id
    JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
    u.id, u.user_name, t.tag
ORDER BY
    u.id, tag_reputation DESC;

现在的结果是：

tag   | tag_reputation |  tag_score
----: | :------------- | :---------
css   |             15 |          0
php   |             10 |          2
mysql |              5 |          1
html  |             -2 |         -1

如您所见，结果是一个标签列表，其中包含按tag_reputation排序的特定用户的信誉和分数。Fiddle
现在我还想计算每个标签的贴子数量。这就是预期的结果：

tag   | tag_reputation |  tag_score | post_num
----: | :------------- | :--------- | :-------
css   |             15 |          0 |        1
php   |             10 |          2 |        2
mysql |              5 |          1 |        1
html  |             -2 |         -1 |        1

我该怎么做？我想我得在post_id栏和GROUP BY子句上下功夫。但我不知道具体是怎么回事。

最佳答案

我用手机发这个，所以我不能用你给的小提琴试试。我使用COUNT修改sql以计算post的数量。

SELECT
t.tag,
sum(r.reputation) AS tag_reputation,
sum(r.score) AS tag_score,
COUNT(DISTINCT pt.post_id) AS post_num
FROM
users u
LEFT JOIN reputations r
    ON r.user_id = u.id
        AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag
ORDER BY
u.id, tag_reputation DESC;

编辑：我用distinct添加count。看看能不能解决。

关于mysql - 我如何计算帖子数量？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46726065/