mysql - MySQL:从子查询到单个查询

我有这个查询，我相信可以优化：

SELECT floors.id, floors.floor FROM floors
WHERE floors.societies_id = 1
AND floors.status = 'Y'
AND floors.id NOT IN (
    SELECT DISTINCT(floors.id) FROM floors
    INNER JOIN societies ON societies.id = floors.societies_id
    INNER JOIN resident_floors ON resident_floors.floors_id = floors.id
    WHERE societies.id = 1
    AND floors.status = 'Y'
)

此查询是否可以使用还是可以改进的？。？

最佳答案

看来您想获取resident_floors中不存在的所有楼层。为此，我们可以保留连接RF并仅询问连接失败导致RF空的行：

SELECT floors.* FROM floors
INNER JOIN societies ON societies.id = floors.societies_id
LEFT JOIN resident_floors ON resident_floors.floors_id = floors.id
WHERE societies.id = 1
AND floors.status = 'Y'
AND resident_floors.floors_id IS NULL