SQL中标记系统的布尔表达式

假设数据->项，单词->名称和agged_item-> agged_items。

这适用于“ tag1 AND（tag2 OR tag3）AND NOT tag4 OR tag5 ”。我敢肯定，其余的您都可以解决。

SELECt items.* FROM items    LEFT JOIN (SELECt i1.item_id FROM tagged_items AS i1 INNER JOIN tags AS t1 ON i1.tag_id = t1.id AND t1.name = 'tag1') AS ti1 ON items.id = ti1.item_id    LEFT JOIN (SELECt i2.item_id FROM tagged_items AS i2 INNER JOIN tags AS t2 ON i2.tag_id = t2.id AND t2.name = 'tag2') AS ti2 ON items.id = ti2.item_id    LEFT JOIN (SELECt i3.item_id FROM tagged_items AS i3 INNER JOIN tags AS t3 ON i3.tag_id = t3.id AND t3.name = 'tag3') AS ti3 ON items.id = ti3.item_id    LEFT JOIN (SELECt i4.item_id FROM tagged_items AS i4 INNER JOIN tags AS t4 ON i4.tag_id = t4.id AND t4.name = 'tag4') AS ti4 ON items.id = ti4.item_id    LEFT JOIN (SELECt i5.item_id FROM tagged_items AS i5 INNER JOIN tags AS t5 ON i5.tag_id = t5.id AND t5.name = 'tag5') AS ti5 ON items.id = ti5.item_idWHERe ti1.item_id IS NOT NULL AND (ti2.item_id IS NOT NULL OR ti3.item_id IS NOT NULL) AND ti4.item_id IS NULL OR ti5.item_id IS NOT NULL;

编辑： 如果要避免子查询，您可以这样做：

SELECt items.* FROM items     LEFT JOIN tagged_items AS i1 ON items.id = i1.item_id LEFT JOIN tags AS t1 ON i1.tag_id = t1.id AND t1.name = 'tag1'    ...WHERe t1.item_id IS NOT NULL ...

我不确定为什么要这么做，因为额外的左联接可能会导致运行速度变慢。