mysql - SQL 跨行检查

Question

假设我有一个下面的表结构。我希望只找到那些仅发出 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  |
+-------+-------+

Answer 1

您可以使用聚合:

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

如果您有一个单独的ip表，那么我建议不存在:

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'
             );