mysql - 多项 IN 运算符 AKA 在 MySQL 中设置交集

Question

mysql> SELECT * FROM nodes
    -> WHERE hostName IN ('localhost', 'm1iafw') OR
    ->        address IN ('localhost', 'm1iafw');
+----------------------+-----------+-----------+
| id                   | hostName  | address   |
+----------------------+-----------+-----------+
|  9131891219333790492 | NULL      | m1iafw    |
| 15289714606300179720 | localhost | NULL      |
| 15886756565768587967 | m1iafw    | 10.7.7.12 |
| 18400354826544934228 | m1iafw    | NULL      |
+----------------------+-----------+-----------+

以上是我正在处理的查询的简化版本。我想用一个 IN 运算符来编写它，这样我就不必多次重复主机名列表。列表中可能有数百个主机名。

我想要的是这样的:

SELECT * FROM nodes
 WHERE (hostName, address) INTERSECTS ('localhost', 'm1iafw');

我不需要 INTERSECT 提供的匹配值列表。我只需要一个像 IN 这样的 bool 结果。集合是否相交。

此外，MySQL 不支持 INTERSECT 运算符。也不WITH。 usual advice是使用连接以不同的形式重写 INTERSECT，但我不知道该怎么做，因为我没有交叉两个表。

此外，我真正想做的甚至有点复杂。给定顶部查询中节点的 ID 号，我想在另一个表中搜索匹配项，其中三列中的任何一列都可能包含匹配 ID 之一。使用我假设的 INTERSECTS 运算符，我可以将查询编写为:

SELECT * FROM alerts
 WHERE (analyzerNodeId, sourceNodeId, targetNodeId) INTERSECTS
           (SELECT id FROM nodes
             WHERE (hostName, address) INTERSECTS ('localhost', 'm1iafw'));

关于我如何做到这一点有什么建议吗？我的 SQL-fu 不是那么强。

Answer 1

对于这样的任务你需要有“临时”表，对于 mysql 你可以这样创建它:

select 'localhost' as hostname
union all
select 'm1iafw'
union all
select ....

因此，要获取此表中主机名或地址所在的所有不同 ID，您可以使用连接语法:

select id
from nodes as n
inner join (
    select 'localhost' as hostname
    union all
    select 'm1iafw'
) as q
on (n.hostName = q.hostname OR n.address = q.hostname)

然后您可以将此查询用作下一个操作的子查询:

SELECT *
FROM alerts as a
inner join
(
    select id
    from nodes as n
    inner join (
        select 'localhost' as hostname
        union all
        select 'm1iafw'
    ) as q
    ON (n.hostName = q.hostname OR n.address = q.hostname)
) as q2
ON (a.analyzerNodeId=q2.id OR a.sourceNodeId=q2.id OR a.targetNodeId=q2.id)