SQL查询中的ALL运算符子句

Question

我有这个表的架构。

Machine​(machine_id​, size)
Operator​(operator_id​, name)
OperationLog​(machine_id, operator_id, date, comment)
  ​machine_id: FK(Machine)
  operator_id: FK(Operator)

假设我想查询只给出操作所有尺寸大于 5m2 的机器的操作员的名称。

使用 ALL 运算符是否会给我想要的结果？也就是说，在尺寸大于 5 的机器列表中，有日志的操作员必须匹配所有这些机器。

SELECT O.name
  FROM Operator O NATURAL JOIN OperationLog L
  WHERE L.machine_id​ = ALL (
      SELECT M.machine_id​     
      FROM Machine M  
      WHERE size >5);

或者我需要像这样进行“双重否定”吗？该怎么办？

SELECT O.name
FROM Operator O
WHERE NOT EXISTS(
   SELECT M.machine_id​
   FROM Machine M 
   EXCEPT
   SELECT L.machine_id​            
   FROM OperationLog L NATURAL JOIN Machine M      
   WHERE L.operator_id = O.operator_id
         AND size >5);

我觉得我把事情复杂化了，可能有更好的方法。

- spacing

---

听起来你正在寻找“关系除法”。这个术语加上“mysql”应该能为你找到很多例子和方法。 - Damien_The_Unbeliever

请添加一些示例表数据和预期结果。（以格式良好的文本形式呈现。） - jarlh

你的ALL子查询只返回一个machine_id（一行或多行）时，结果为true。 - dnoeth

对于每个操作员，计算他操作的唯一机器数量，其尺寸大于5，并将其与尺寸大于5的机器上的机器数量进行比较，仅返回那些数字相等的操作员。 - mounaim

Answer 1

统计每个操作员使用的(size >= 5)不同机器的数量，并将该数字与此类机器的总数进行比较：

SELECT op.name
FROM operator op
    INNER JOIN operationlog l
        ON op.operator_id = l.operator_id
    INNER JOIN machine m
        ON l.machine_id
WHERE m.size >= 5
GROUP BY op.name
HAVING COUNT(DISTINCT m.machine_id) = (
        SELECT COUNT(*)
        FROM machine
        WHERE size >= 5
)

- Serge

Answer 2

我喜欢使用group by和having来达到这个目的：

SELECT O.name
FROM Operator O JOIN
     OperationLog L
     ON L.operator_id = O.operator_id JOIN
     Machine M 
     ON L.machine_id​ = M.machine_id​ 
WHERE M.size > 5
GROUP BY O.name  
HAVING COUNT(DISTINCT M.machine_id) = (SELECT COUNT(DISTINCT M2.machine_id)
                                       FROM Machine M2  
                                       WHERE size > 5
                                      );

如果表中没有重复项，则使用COUNT(*)而不是COUNT(DISTINCT)。

我要注意的是，我强烈反对使用NATURAL JOIN。这是一个等待发生错误的漏洞。为什么？它只是使用具有相同名称的列。它甚至不使用相同的类型。例如，我创建的大多数表都有CreatedBy和CreatedAt列，它将使用这些列。

- Gordon Linoff

---

machine_id 可能是 machine 表中的主键。如果是这样的话，在子查询中使用 COUNT(*) 就足够了，而不需要使用 COUNT(DISTINCT M2.machine_id)。除此之外，我们的查询语句是相同的 :)。 - Serge