我有这个表模式。
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);
我觉得我太复杂了,可能有更好的方法。
计算每个操作员使用的不同机器数量(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
)
我很喜欢为此目的使用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
的列,它将使用这些列。