子查询中的MySQL交

Question

我试图为(公寓)列表创建一个过滤器，通过apartment features表与apartsments_features建立多到多的关系。

我只想包括一些功能的 all (在表单上标记为“是”)的公寓，不包括所有具有任何另一套功能的公寓(标记为“No”)。我意识到我不能在INTERSECT或MINUS中使用MySQL太晚了。

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

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

Answer 2

你可以试试这样的方法：

SELECT apartment_id
FROM
(
    SELECT apartment_id
    FROM apartments_features
    WHERE feature_id IN (103, 106)
    GROUP BY apartment_id
    HAVING COUNT(*) = 2
) T1
LEFT JOIN
(
    SELECT apartment_id
    FROM apartments_features
    WHERE feature_id IN (105, 107)
) T2
ON T1.apartment_id = T2.apartment_id
WHERE T2.apartment_id IS NULL

将此查询的结果加入到“公寓”表中，以获取名称等。