MySql XNOR 等效项

Question

我正在编写一个 SQL 查询，用于检查其 where 子句中的错误设备。为了执行这个操作，我需要一个一个地检查每个设备的修订周期，所以这个条款将结果限制为正确的设备(没有任何周期不正确):

where not exists(select * from trevision_cycle cycle 
    where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK')

但是，现在我想引入一个查询参数，以便根据其值仅检索正确的设备或仅检索不正确的设备。假设我称它为不正确，所以我想做与此处所做的相同:

where (not incorrect and not exists(select * from trevision_cycle cycle 
    where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK'))
or (incorrect and exists (select * from trevision_cycle cycle 
    where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK'))

因此，如果存在 incorrect 标志，请检查至少有一个错误修订周期的设备。否则，只取回所有正确的设备。 虽然查询看起来很多余，但使用逻辑 XNOR 可以实现相同的结果.

对此我有更好的选择吗？

更新

示例数据

Equipment 1 -> Has one NO_OK cycle
Equipment 2 -> All its cycles are OK

Query with incorrect = true -> Returns only Equipment 1
Query with incorrect = false -> Returns only Equipment 2

Answer 1

您可以将 XOR 与 NOT 结合使用 - 两者都存在于 mysql 中。这是 XOR 的真值表:

+----------------+|A | B | A XOR B |+--+---+---------+|0 | 0 | 0       ||0 | 1 | 1       ||1 | 0 | 1       ||1 | 1 | 0       |+----------------+

If we apply NOT to this table, it will look this:

+---------------------+|A | B | NOT(A XOR B) |+--+---+--------------+|0 | 0 | 1            ||0 | 1 | 0            ||1 | 0 | 0            ||1 | 1 | 1            |+---------------------+

Same as the XNOR table. So, WHERE can look like this:

WHERE NOT (incorrect XOR EXISTS(SELECT * FROM trevision_cycle cycle 
WHERE cycle.id_equipment = equip.id AND cycle.`status` = 'NO_OK'))