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

Question

鉴于我的以下表结构：

我正在尝试构建一个查询，该查询将返回失败登录尝试次数。

例如，您会注意到过去一小时内来自单个 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

您可以尝试这两种方法并看看哪一个对您来说更快。