我有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/

10-12 01:01