mysql - 棘手的:具有行值的左联接关系

假设我有以下两个表：

PRICE
price_id    price       room_id     nr_of_people
1           80          1           1
2           75          1           2
3           90          2           2
4           120         3           3


ROOM
room_id     room_no     max_people
1           101         2
2           102         3
3           103         4

而且，我需要以下结果：

QUERY_RESULT
price       room_no     nr_of_people
80          101         1
75          101         2
0           102         1
90          102         2
0           102         3
0           103         1
0           103         2
120         103         3
0           103         4

这里最棘手的部分是，如果没有可用的实际数据，我需要检索每个人的价格（2人，3人，4人；即递增到房间表中定义的max_people）在价格表中，默认情况下应填写0。上面的插图应支持我的解释。

我不确定表结构是否有任何逻辑错误。

非常感谢您对解决问题的任何想法/意见/帮助。

最佳答案

正如abresas的回答和xQbert的注释所建议的那样，您需要以某种方式创建数据以便将其与表联接。

像abresas的答案一样，我使用了一个辅助表，但是在我的解决方案中，该表仅需要填充数字1到N，其中N =可以出现在列max_people上的最大值。

我用单列aux创建了一个名为num的辅助表。这个查询对我有用：

SELECT IF(price.price IS NULL, 0, price.price) AS price, room.room_no, aux.num AS nr_of_people
FROM room
JOIN aux ON aux.num <= room.max_people
LEFT JOIN price ON ( price.room_id = room.room_id
AND aux.num = price.nr_of_people )
ORDER BY room.room_id, num

不幸的是，mysql没有提供生成整数序列的本机机制（请参见these questions），因此，物理上创建辅助表似乎是实现所需功能的最实用方法，尽管如果存在某些变通办法，您确实不能或不想创建这样的表。

只是为了好玩，以下操作无需创建新表即可工作（所有工作均受到我链接的问题的启发）：

SELECT [...]
FROM room
JOIN (
    SELECT 1 num
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    -- ...add as many entries as needed...
) aux ON aux.num <= room.max_people
LEFT JOIN [...]

以及：

SELECT [...]
FROM room
JOIN (
    SELECT @row := @row +1 AS num
    FROM any_table_that_is_big_enough, (SELECT @row :=0) r
) aux ON aux.num <= room.max_people
LEFT JOIN [...]