如何计算帖子数? [英] How can I count the number of posts?
问题描述
这是我的表结构:
-- 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
排序的特定用户的声誉和分数标签列表. 小提琴
As you can see, the result is a list of tags with reputations and scores for a specific user ordered by tag_reputation
. Fiddle
现在,我还要统计每个标签的帖子数.所以这是预期结果:
Now I want to also count the number of posts for each tag. So this is the expected result:
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
子句.但是我不知道到底是什么.
How can I do that? I think I have to work on post_id
column and GROUP BY
clause. But I don't know how exactly.
推荐答案
我使用手机发布了此信息,因此无法在您提供的小提琴上尝试使用.我修改了您的SQL以使用COUNT
计算帖子数.
I post this using mobile phone so I cannot try it on the fiddle you gave. I modify your SQL to count number of post using COUNT
.
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;
我将COUNT与DISTINCT相加.看看能否解决.
I add COUNT with DISTINCT. See if it solve.
这篇关于如何计算帖子数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!