sql - 子查询中的 MySQL 交集

Question

我正在尝试为(公寓)列表创建过滤器，通过 apartsments_features 表与 apartment features 建立多对多关系。

我只想包括具有所有某些功能(在表格上标记为"is")的公寓，不包括具有任何其他设置功能(标记为“否”)的所有公寓。我意识到我不能在 MySQL 中使用 INTERSECT 或 MINUS 为时已晚。

我有一个类似这样的查询:

SELECT `apartments`.* FROM `apartments` WHERE `apartments`.`id` IN (
    SELECT `apartments`.`id` FROM `apartments` INTERSECT (
        SELECT `apartment_id` FROM `apartments_features` WHERE `feature_id` = 103 
INTERSECT SELECT `apartment_id` FROM `apartments_features` WHERE `feature_id` = 106
    ) MINUS (
    SELECT `apartment_id` FROM `apartments_features` WHERE `feature_id` = 105 UNION 
    SELECT `apartment_id` FROM `apartments_features` WHERE `feature_id` = 107)
)
ORDER BY `apartments`.`name` ASC

我很确定有办法做到这一点，但目前我的知识仅限于简单的左右连接。

Answer 1

稍微不同的做法:

select a.*
from apartments a
join apartments_features f1 
on a.apartment_id = f1.apartment_id and f1.feature_id in (103,106) -- applicable features
where not exists
(select null from apartments_features f2
 where a.apartment_id = f2.apartment_id and f2.feature_id in (105,107) ) -- excluded features
group by f1.apartment_id
having count(*) = 2 -- number of applicable features