mysql - MySQL to not return same results

Question

I have a table with list of customers:

customer

c_id    c_name      c_email     c_role
1       abc1        [e-mail]  Dev
2       abc2        [e-mail]  Dev
3       abc3        [e-mail]  Dev
4       abc4        [e-mail]  Dev
5       abc5        [e-mail]  Dev
6       abc6        [e-mail]  Dev
7       abc7        [e-mail]  Dev
8       abc8        [e-mail]  Dev
9       abc9        [e-mail]  Dev

I query the table in the following way:

select * from customer where c_role = 'Dev' order by c_id limit 2;

So, I get the results with:

c_id    c_name      c_email     c_role
1       abc1        [e-mail]  Dev
2       abc2        [e-mail]  Dev

The business requirements says that if any records are accessed by a set of users for last 3 days, then those should not return in the subsequent query output.

So, if the user runs a query again for the next 3 days:

select * from customer where c_role = 'Dev' order by c_id limit 2;

The result should be:

c_id    c_name      c_email     c_role
3       abc3        [e-mail]  Dev
4       abc4        [e-mail]  Dev

Can anyone help me how to create this kind of rule in MySQL?

标签： mysqlsqlgreatest-n-per-group

Answer 1

在当前表中添加新列对您没有帮助。

您必须创建另一个表来存储用户访问过的所有 c_id 以及执行查询的日期时间。

CREATE TABLE IF NOT EXISTS `access_record` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `c_id` INT(11) NOT NULL , // id of the record which user accessed
    `user_id` INT(11) NOT NULL , // id of the user who accessed the record
    `accessed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (`id`)
);

因此，每当用户运行下一个查询时，您都可以使用此表来了解用户是否已经访问过记录，然后使用这些 c_ids 将它们从下一个结果集中排除。

SELECT
    c.c_id, c.c_role,c.c_name,c.c_email
FROM
    customer AS c
WHERE
    c.c_role = 'Dev'
    AND c.c_id NOT IN (
        SELECT
            ar.c_id
        FROM
            access_record AS ar
        WHERE ar.user_id = 1  // ofcourse this will change with each user (current user in your case I assume)
            AND ar.accessed_at > DATE_SUB(NOW(), INTERVAL 3 DAY)
     )
     ORDER BY c.c_id LIMIT 2;

这将为您提供过去 3 天内特定用户未访问的所有记录。

我希望这有帮助。

在评论中回答@dang 的问题

查询运行时如何填充 access_record？

获取所有记录后，从这些记录中提取 c_ids，然后将这些 c_ids 插入access_record表中。

在 MYSQL 中，这个查询应该可以解决问题

INSERT INTO access_record (c_id,user_id)

SELECT
    c.c_id, 1 // user_id of the user who is fetching records
FROM
    customer AS c
WHERE
    c.c_role = 'Dev'
    AND c.c_id NOT IN (
        SELECT
            ar.c_id
        FROM
            access_record AS ar
        WHERE ar.user_id = 1  // ofcourse this will change with each user (current user in your case I assume)
            AND ar.accessed_at > DATE_SUB(NOW(), INTERVAL 3 DAY)
     )
     ORDER BY c.c_id LIMIT 2;

您还可以使用一个查询获取这些 c_id，然后使用第二个查询将这些 c_id 插入到 access_record 表中。

$records如果您已获取所有记录

$c_ids = array_column($temp, 'c_id'); // get all c_ids from fetched record array

现在运行查询以插入所有这些 c_id。