我有2张桌子:
实体:
id
状态:
id
entity_id
status
每个实体可以具有许多状态,我只需要选择具有“已付款”状态而没有“已取消”状态的实体。我该如何正确加入?
例:
entities: id 7
entities: id 8
entities: id 9
entities: id 10
statuses: id 1, entity_id 7, status 'paid'
statuses: id 2, entity_id 7, status 'canceled'
statuses: id 3, entity_id 8, status 'paid'
statuses: id 4, entity_id 10, status 'onhold'
statuses: id 5, entity_id 8, status 'whatever'
仅ID为
8
的条目将被选中一次。 最佳答案
也许像下面这样:SELECT e.*FROMentities as e INNER JOIN statuses as spaid ON e.id = spaid.entity_id and spaid.status = 'paid' LEFT JOIN statuses as scanceled ON e.id = scanceled.entity_id and scanceled.status = 'canceled'WHERE scanceled.id IS NULL
第一个联接采用具有“已付款”状态的实体。
第二个联接将为状态为“已取消”的实体添加第二个状态,为没有取消状态的实体添加NULL
然后,where子句将过滤条目,仅将具有NULL的行作为“取消”状态。
关于mysql - MySQL:选择相对于第二张表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/45434472/