mysql - 棘手 : Left join relation with row value

Question

假设我有以下两个表:

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。上图应该支持我的解释。

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

非常感谢有关如何解决该问题的任何想法/意见/帮助。

Answer 1

正如 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不提供生成整数序列的 native 机制(请参阅 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 [...]