mysql - 查询以选择自上次成功以来的所有行

Question

鉴于我的以下表格结构:

我正在尝试构建一个查询，该查询将返回每个 IP 自过去一小时内同一 IP 的最后一次成功登录以来的失败登录尝试次数。

例如，您会注意到在过去一小时内来自单个 IP 的几次失败尝试 (0)，但是自上次成功登录 (#145) 以来只有 1 ( #146)，这是我想要返回的。

此查询也应该是动态的并返回分组 IP 的行。

到目前为止，这是我所拥有的，但我认为 @ipa 正在返回 NULL

SELECT COUNT(*) tries, @ipa := login_ip
FROM login_log
WHERE login_id > (
    SELECT MAX(login_id)
    FROM login_log
    WHERE login_success = 1
    AND login_ip = @ipa
)
AND login_success = 0
AND login_date > NOW() - 3600
GROUP BY login_ip
ORDER BY tries DESC;

谢谢

Answer 1

为表设置别名并以这种方式尝试，因为您的 @ipa 变量对您没有任何作用:

SELECT COUNT(1) tries, l.login_ip
FROM login_log l
WHERE login_id > (
    SELECT MAX(login_id)
    FROM login_log l2
    WHERE l2.login_success = 1
    AND l2.login_ip = l.login_ip
)
AND login_success = 0
AND login_date > NOW() - 3600
GROUP BY login_ip
ORDER BY tries DESC;

此外，您可以使用 join 执行此操作:

select
    count(1) tries,
    log1.login_ip
from
    login_log log1
    inner join (
        select
            login_ip,
            max(login_date) as max_date
        from
            login_log
        where
            login_success = 1
        group by
            login_ip
    ) log2 on
        log1.login_ip = log2.login_ip
where
    log1.login_success = 0
    and log1.login_date > NOW() - 3600
    and log1.login_date > log2.max_date
group by
    login_ip
order by tries desc

您可以尝试两种方式，看看哪种方式对您来说更快。