MySQL多表关联查询：筛选包含room_status=24的房屋

先理清楚三张表的关联逻辑：Houses和Rooms通过中间表Houses_Rooms形成多对多关系——一个房屋可以对应多个房间，一个房间只属于一个房屋。针对你提到的需求，我分两种最常见的场景给出具体SQL方案：

场景1：获取至少包含一个状态为24的房间的房屋 #

不管该房屋是否还有其他状态的房间，只要它存在至少一个room_status=24的房间，就纳入结果。

SQL语句： #

SELECT DISTINCT h.id, h.title, h.description
FROM Houses h
JOIN Houses_Rooms hr ON h.id = hr.house_id
JOIN Rooms r ON hr.room_id = r.id
WHERE r.room_status = 24;

逻辑说明： #

1. 通过Houses_Rooms关联房屋与房间，建立完整的关联关系链
2. 筛选出房间状态为24的记录
3. 用DISTINCT去重：避免同一个房屋因有多个24状态房间而重复返回

用你的示例数据，这个查询会返回id为1和2的房屋：

• 房屋1有Green room（状态24）和Yellow room（状态25），满足“至少一个24状态房间”
• 房屋2只有Blue room（状态24），符合条件

场景2：获取所有房间状态都是24的房屋 #

要求房屋的每一个房间状态都必须是24，不能存在其他状态的房间。

写法一（分组统计法）： #

SELECT h.id, h.title, h.description
FROM Houses h
JOIN Houses_Rooms hr ON h.id = hr.house_id
JOIN Rooms r ON hr.room_id = r.id
GROUP BY h.id, h.title, h.description
HAVING COUNT(DISTINCT r.room_status) = 1 
   AND MAX(r.room_status) = 24;

逻辑说明： #

1. 关联三张表后按房屋分组
2. COUNT(DISTINCT r.room_status) = 1确保该房屋的房间状态只有一种
3. MAX(r.room_status) = 24确认这唯一的状态就是24

用示例数据，这个查询只会返回id为2的房屋：房屋1存在两种状态的房间（24和25），不符合要求。

写法二（存在性判断法，更直观）： #

SELECT h.id, h.title, h.description
FROM Houses h
WHERE NOT EXISTS (
    SELECT 1
    FROM Houses_Rooms hr
    JOIN Rooms r ON hr.room_id = r.id
    WHERE hr.house_id = h.id
      AND r.room_status != 24
)
AND EXISTS (
    SELECT 1
    FROM Houses_Rooms hr
    JOIN Rooms r ON hr.room_id = r.id
    WHERE hr.house_id = h.id
      AND r.room_status = 24
);

逻辑说明： #

1. 第一个NOT EXISTS确保该房屋没有任何状态非24的房间
2. 第二个EXISTS确保该房屋至少有一个状态为24的房间（避免筛选出无房间的空房屋）

内容的提问来源于stack exchange，提问作者vasya