进行计数和 distinct 以检查帖子被阅读的次数 [英] Doing a count along with distinct to check number of times a post was read

问题描述

我在 Mysql 中有这个表，我用它来收集用户查看帖子的次数的数据.reader_id 是阅读帖子的用户 ID，read_pub_id 是发布/撰写帖子的人的 ID，而 read_artc_id 是帖子的 ID.

I have this table in Mysql which i use to collect data for the number of times a users views a post. reader_id is the id of the user reading the post, read_pub_id is the id of the person who published/wrote the post and read_artc_id is the post's id.

它的创建是这样的:

CREATE TABLE `reads_t` (
    `reader_id` INT(10) NOT NULL DEFAULT '0',
    `read_pub_id` INT(10) NOT NULL DEFAULT '0',
    `read_artc_id` INT(10) NOT NULL DEFAULT '0',
    PRIMARY KEY (`reader_id`, `read_pub_id`, `read_artc_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

然后我在每次阅读帖子时使用 Insert Ignore 语句，如下所示:

Then I use a Insert Ignore statement each time a post is read, like this:

insert ignore into reads_t (reader_id, read_pub_id, read_artc_id) values (3,2,1);

所以我最终得到这样的数据:

So I end up with data like this:

reader_id; read_pub_id; read_artc_id
        1;           1;           1
        1;           1;           2
        1;           1;           3
        2;           1;           1
        2;           1;           2
        2;           1;           3
        2;           2;           2
        2;           2;           3
        3;           1;           1
        3;           2;           1
        3;           2;           2

在这里，用户 1 阅读了发布者 1 的文章 1，依此类推.

Here, user 1 read publisher 1's article 1 and so on.

我如何执行 sql 以便最终得到这样的数据:它是发布者发布的帖子被读者阅读的总次数.稍后我将使用它来更新每个帖子的阅读"列.

How do i do the sql so I can end up with data like this: It's the total times a post by a publisher was read by a reader. I'll later use this to update the 'read' column for each post.

read_pub_id ; read_artc_id ; times_read
          1              1            3
          1              2            2
          1              3            2
          2              1            1
          2              2            2
          2              3            1

我可以看到这无法在我习惯的简单选择语句中完成.我尝试了下面的一个，但这给了我奇怪的结果.

I can see this can't be done in a simple select statement that I'm used to. I tried the one below, but that gives me strange results.

select read_pub_id, read_artc_id, count(read_artc_id) as times_read from reads_t;

推荐答案

你只需要添加 GROUP BY 子句，像这样:

You just need to add the GROUP BY clause, like this:

SELECT
  read_pub_id,
  read_artc_id,
  count(read_artc_id) as times_read
FROM
  reads_t
GROUP BY
  read_pub_id,
  read_artc_id;

:)

这篇关于进行计数和 distinct 以检查帖子被阅读的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！