mysql - SQL 查询 - 比较临时表中的所有行

Question

我有一张 table Rooms和一个临时表Room_Nums

Rooms 的列表:

Rooms(
 ID,
 room_name,
 max_Adults,
 max_Children
);

-- Populate
INSERT INTO Rooms VALUES (1,"Room A",2,1),(2,"Room B",2,5),(3,"Room C",3,1);

临时表:

DROP TEMPORARY TABLE IF EXISTS Room_Nums;
CREATE TEMPORARY TABLE Room_Nums(
 ID int(1),
 Adults int(2),
 Children int(2)
);

-- Populate
INSERT INTO Room_Nums VALUES (1,2,0),(2,2,5);

<小时/>

我想仅选择 Rooms有 max_adults + max_children <= 中所有行的小于或等于 ( Room_Nums )以及来自 Room_Nums 的 ID

编辑
这是MySQL。

如果Room_Nums有 2 行，这意味着我要找 2 个匹配 Adults 的房间+ Children例如。

SELECT r.room_name, rn.Adults, rn.Children, rn.ID
FROM Rooms r
     OUTER JOIN Room_Nums rn 
         ON (rn.Adults <= r.max_Adults AND rn.Children <= r.max_Children )

编辑2
要求:

• 1 间客房:2 名成人和 0 名 child
• 1 间客房:2 名成人和 5 名 child

结果应该是:

-----------------------------------------------
| Room Name | Adults | Children | Room_Nums.ID |
-----------------------------------------------
| Room A    |   2    |    0     |    1         |
-----------------------------------------------
| Room B    |   2    |    5     |    1         |
-----------------------------------------------
| Room B    |   2    |    5     |    2         |
-----------------------------------------------

如果无法从 Room_Nums 中找到行，则显示空结果.

Answer 1

虽然不太清楚“所有行”是什么意思，也许这会起作用

 Select room_name,  max_adults + max_children, Room_nums.ID
 from Rooms 
 left join Room_nums 
 on Rooms.id=Room_nums.id 
 WHERE max_adults + max_children <= (select min(adults + children) from Room_nums);