MySQL : SELECT all users with 5 unopened messages in there last 5 messages received in a messages table

Question

我使用的是mysql。

我有一个包含 userid、message_id、opened(true 或 false)、时间戳的消息表。

我希望所有未打开最近收到的 5 条消息的用户

这就是我现在拥有的:

SELECT mnc.userid
FROM  `messages` mnc
WHERE (select count(*) from messagesas m where m.userid = mnc.userid
        and m.message_sendtime_timestamp >= mnc.message_sendtime_timestamp 
    and m.opened = 'FALSE') >= 6

但是，这给了我超过 6 条未打开消息的用户不一定是连续的

这里是示例数据

CREATE TABLE messages
    (`user_id` int, `timestamp` datetime, `opened` varchar(5))
;

INSERT INTO messages
    (`user_id`, `timestamp`, `opened`)
VALUES
    (1, '2016-01-01 00:00:00', 'false'),
    (1, '2016-02-01 00:00:00', 'false'),
    (1, '2016-03-01 00:00:00', 'false'),
    (1, '2016-04-01 00:00:00', 'false'),
    (1, '2016-05-01 00:00:00', 'false'),
    (1, '2016-06-01 00:00:00', 'false'),
    (2, '2016-01-01 00:00:00', 'false'),
    (2, '2016-02-01 00:00:00', 'false'),
    (2, '2016-03-01 00:00:00', 'false'),
    (3, '2015-01-01 00:00:00', 'false'),
    (3, '2016-01-01 00:00:00', 'false'),
    (3, '2016-02-01 00:00:00', 'false'),
    (3, '2016-03-01 00:00:00', 'false'),
    (3, '2016-04-01 00:00:00', 'false'),
    (3, '2016-05-01 00:00:00', 'true'),
    (3, '2016-06-01 00:00:00', 'false'),
    (4, '2015-01-01 00:00:00', 'true'),
    (4, '2015-02-01 00:00:00', 'true'),
    (4, '2016-01-01 00:00:00', 'false'),
    (4, '2016-02-01 00:00:00', 'false'),
    (4, '2016-03-01 00:00:00', 'false'),
    (4, '2016-04-01 00:00:00', 'false'),
    (4, '2016-05-01 00:00:00', 'false'),
    (4, '2016-06-01 00:00:00', 'false')

预期结果:

userid 
1 
4

Answer 1

回答这个问题

我想要所有在最近收到的 5 条消息中未打开消息的用户吗？

首先，您需要为每个user_id创建一个row_id

SELECT @rowid := IF(@prev_value = user_id,  @rowid + 1,  1) as row_id,
       m.*
       @prev_value := user_id
FROM messages m,
     (SELECT @row_num := 1) x,
     (SELECT @prev_value := '') y
ORDER BY `timestamp` DESC

然后检查该子查询有多少条打开的消息

<强> SQL Fiddle Demo

SELECT user_id, COUNT(*), SUM(opened = 'false')
FROM (
       SELECT @rowid := IF(@prev_value = user_id,  @rowid + 1,  1) as row_id,
              m.*,
              @prev_value := user_id
       FROM messages m,
            (SELECT @row_num := 1) x,
            (SELECT @prev_value := '') y
       ORDER BY user_id, `timestamp` DESC
     ) T
WHERE row_id <= 5   -- only check last 5 or less messages
GROUP BY user_id
HAVING COUNT(*) = SUM(opened = 'false') -- Check all messages are NOT opened