时间戳

首页 > 解决方案 > 跨行的 SQL 检查

mysql - 跨行的 SQL 检查

问题描述

假设我在下面有一个表结构。我只想找到那些只发出 HEAD 请求的 IP。在这种情况下，IP6。

mysql> select distinct ip, method from request_records;
+-------+---------+
| ip    | method   |
+-------+---------+
| IP1   | GET     |
| IP1   | POST    |
| IP1   | OPTIONS |
| IP1   | HEAD    |
| IP2   | GET     |
| IP2   | POST    |
| IP2   | OPTIONS |
| IP2   | HEAD    |
| IP3   | GET     |
| IP4   | POST    |
| IP5   | OPTIONS |
| IP6   | HEAD    |
+-------+---------+

使用 NOT IN 条件会返回其他不正确的 IP（见下文）。如何检查多行

mysql> select distinct ip,method from request_records where method not in ('GET','POST','OPTIONS');
+-------+-------+
|  ip   | method |
+-------+-------+
| IP1   |  HEAD  |
| IP2   |  HEAD  |
| IP6   |  HEAD  |
+-------+-------+

标签： mysqlsql

解决方案

您可以使用聚合：

select ip
from request_records
group by ip
having min(request) = max(request) and min(request) = 'HEAD';

如果您有一个单独的唯一表ip，那么我建议not exists：

select ip
from ips
where not exists (select 1
                  from request_record rr
                  where rr.ip = ips.ip and
                        rr.request <> 'HEAD'
                 );

当然，这将返回没有'HEAD'-- 这可能是您想要的也可能不是您想要的行。你可以加：

select ip
from ips
where not exists (select 1
                  from request_record rr
                  where rr.ip = ips.ip and
                        rr.request <> 'HEAD'
                 ) and
      exists (select 1
              from request_record rr
              where rr.ip = ips.ip and
                    rr.request = 'HEAD'
             );

推荐阅读