mysql - MySQL连接价格，房间和季节的表

我有三个表，用户可以存储季节，房间和价格。为了更好地理解我想要达到的目标，此处介绍了表“季节”，并且该表将不在查询中使用。

表是：

    table seasons

    |usr_id | season_id | season_name |
    |-------|-----------|-------------|
    |   1   |     1     |     June    |
    |   1   |     2     |     July    |
    |   1   |     3     |    August   |


    table rooms

    |usr_id | room_id |   room_name   |
    |-------|---------|---------------|
    |  1    |    1    |    Studio_1   |
    |  1    |    2    |    Studio_2   |
    |  5    |    3    |  Other studio |


    table prices

    |usr_id | price_id | room_id | season_id | price |
    |-------|----------|---------|-----------|-------|
    |   1   |     1    |    1    |     1     |  10   |
    |   1   |     2    |    2    |     1     |  100  |
    |   1   |     3    |    1    |     2     |  20   |
    |   1   |     4    |    2    |     2     |  200  |
    |   1   |     5    |    1    |     3     |  30   |
    |   1   |     6    |    2    |     3     |  300  |

我想查询数据库并获取价格，在本示例中，每个季节每个房间的user_id = 1。我期望得到的是：

studio_1 | 10 | 20 | 30 |
---------|----|----|----|

studio_2 | 100 | 200 | 300 |
---------|-----|-----|-----|

我试图加入表“房间”和“价格”，但无法获得正确的结果。我用以下

    SELECT r.room_name, p.price
    FROM rooms AS r
    LEFT JOIN prices AS p ON r.room_id = p.room_id
    WHERE p.usr_id = 1

每个用户都可以添加“无限”的季节和房间

有什么建议吗？
谢谢

最佳答案

您正在描述数据透视表。您可以使用GROUP BY将结果减少为每个room_id一行，然后使用条件表达式仅显示每个季节的价格。

SELECT r.room_name,
    MAX(CASE p.season_id WHEN 1 THEN p.price END) AS price_season_1,
    MAX(CASE p.season_id WHEN 2 THEN p.price END) AS price_season_2,
    MAX(CASE p.season_id WHEN 3 THEN p.price END) AS price_season_3,
    MAX(CASE p.season_id WHEN 4 THEN p.price END) AS price_season_4
FROM rooms AS r
LEFT JOIN prices AS p ON r.room_id = p.room_id
WHERE p.usr_id = $usr_id
GROUP BY r.room_id

在编写数据透视表查询时，必须对每个列进行硬编码，并为每个对应的值编写一个表达式。



发表您的评论：


  每个用户的季节数并不相同，因为每个用户将能够创建他所需的任意多个季节。


然后，您需要执行前面的查询，以便您的应用程序可以发现给定用户所需的季节。

SELECT season_id, season_name FROM seasons where usr_id = 1

然后使用该数据在该用户所需的选择列表中形成尽可能多的列。使用应用程序代码执行此操作。

如果您了解数据透视表，就会发现这始终是必需的。您必须先知道这些列，然后才能编写查询。 SQL无法根据其发现的数据值在执行过程中添加更多列。