mysql - mysql 中的复杂旋转

Question

我在 MySQL 中有一个包含数据的表，我想从该表中实现某种数据透视，这对我来说变得很复杂。我尝试过查看 this但似乎没有什么对我有用。这是我的表的结构:

roomid| day| 1 | 2 | 3 | 4 | 5 | 6 |
------+----+---+---+---+---+---+---+
1     | 1  |BIO|   |   |   |   |   |
1     | 2  |   |CHE|   |   |   |   |
1     | 3  |   |   |   |   |   |ENG|     
1     | 4  |   |   |KIS|   |   |   |
1     | 5  |   |   |   |   |   |PHY|  
2     | 1  |BIO|   |   |   |   |   | 
2     | 2  |   |CHE|   |   |   |   |
2     | 3  |   |   |   |   |ENG|   |     
2     | 4  |   |   |KIS|   |   |   | 
2     | 5  |   |   |   |   |   |PHY|     

该表保存时间表数据，roomid 是房间 ID，day 是从周一到周五(1 到 5)的天数。第 1 至 6 列是周期 ID。我需要组织数据以获得显示每天每个类(class)的时段 ID 的结果。像这样的东西:

|roomid| 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 |
-------+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+
1      |BIO|   |   |   |   |   |   |CHE|   |   |   |   |   |   |   |   |   |ENG|   |   |KIS|   |   |   |   |   |   |   |   |PHY|
2      |BIO|   |   |   |   |   |   |CHE|   |   |   |   |   |   |   |   |   |ENG|   |   |KIS|   |   |   |   |   |   |   |   |PHY|

请注意，不同日期的经期 ID 会重复出现。

Answer 1

您可以使用条件聚合:

select room_id,
       max(case when day = 1 then slot_1 end) as day_1_slot_1,
       max(case when day = 1 then slot_2 end) as day_1_slot_2,
       . . . 
       max(case when day = 2 then slot_1 end) as day_2_slot_1,
       max(case when day = 2 then slot_2 end) as day_2_slot_2,
       . . . 
from schedule s
group by room_id