mysql - 在间接相关表中的项目之间创建任意的 1-1 对应关系

Question

考虑以下表格:

Rooms
+------+-------+
|  ID  | Room  | 
+------+-------+
|   1  | A101  |
|   2  | A102  |
|   3  | A103  |
|   4  | A101o |
|   5  | A102o |
|   6  | A103o |
+------+-------+

Beds
+------+---------+
|  ID  | RoomId  |
+------+---------+
|   1  |    1    |
|   2  |    1    |
|   3  |    2    |
|   4  |    2    |
|   5  |    3    |
|   6  |    3    |
|   7  |    4    |
|   8  |    4    |
|   9  |    5    |
|  10  |    5    |
|  11  |    6    |
|  12  |    6    |
+------+---------+

每个房间都有一定数量的床(通常是 2 张)。我想对带有任意 1-1 对床的房间进行自我连接(即低于 1-7 和 2-8 是成对的，但 1-8 和 2-7 也一样好。但我不想要所有可能的配对，即我不想要 1-7, 1-8, 2-7, 2-8 .... 我只希望每张床都与相应房间中的另一张床完全配对.

+--------+--------+--------+---------+
| Room 1 | Bed 1  | Room 2 | Bed 2   |
+--------+--------+--------+---------+
|  A101  |    1   | A101o  |    7    |
|  A101  |    2   | A101o  |    8    |
|  A102  |    3   | A102o  |    9    |
|  A102  |    4   | A102o  |   10    |
|  A103  |    5   | A103o  |   11    |
|  A103  |    6   | A103o  |   12    |
+--------+--------+--------+---------+

请注意，我的实际数据的排序并不是那么整齐——但我知道相应房间的床位数量是相等的。如果A102有3张床，A102o也会有3张床，以此类推。
这很接近，但给了我重复:

SELECT beds.bed_id, rooms.room, rooms2.room as room2, beds2.bed_id AS bed_id2 
  FROM beds
  LEFT JOIN rooms ON (beds.room_id = rooms.room_id)
  CROSS JOIN rooms rooms2 ON (CONCAT(rooms.room,'o') = rooms2.room)
  JOIN beds beds2 ON (beds2.room_id = rooms2.room_id);

Answer 1

如果您只希望每对相关房间有两行:

select r1.room 'Room 1',if(which_row=1,min(b1.bed_id),max(b1.bed_id)) 'Bed 1',r2.room 'Room 2',if(which_row=1,min(b2.bed_id),max(b2.bed_id)) 'Bed 2'
from (select 1 which_row union all select 2) which_row
cross join rooms r1
join rooms r2 on r2.room=concat(r1.room,'o')
join beds b1 on b1.room_id=r1.room_id
join beds b2 on b2.room_id=r2.room_id
group by r1.room_id,r2.room_id,which_row

如果您想要的行数与床位一样多(最多四张)，它基本上是相同的，但是获取每行床位的表达式稍微复杂一些，您需要一个子查询来获取床位的数量每对房间:

select
    room1 'Room 1',
    substring_index(substring_index(beds1, ',', which_row), ',', -1) 'Bed 1',
    room2 'Room 2',
    substring_index(substring_index(beds2, ',', which_row), ',', -1) 'Bed 2'
from (
    select
        r1.room room1,
        group_concat(distinct b1.bed_id order by b1.bed_id) beds1,
        r2.room room2,
        group_concat(distinct b2.bed_id order by b2.bed_id) beds2,
        least(count(distinct b1.bed_id),count(distinct b2.bed_id)) beds
    from rooms r1
    join rooms r2 on r2.room=concat(r1.room,'o')
    join beds b1 on b1.room_id=r1.room_id
    join beds b2 on b2.room_id=r2.room_id
    group by r1.room, r2.room
) room_pairs
join (
    select 1 which_row union all select 2 union all select 3 union all select 4
) which_row on which_row <= room_pairs.beds

将其分块构建，您希望每对房间在结果中最多有四行。因此，您可以使用一个子查询，该子查询可以连接到查询的其余部分，从而导致所有其他行重复:

select 1 which_row union all select 2 union all select 3 union all select 4

+-----------+
| which_row |
+-----------+
|         1 |
|         2 |
|         3 |
|         4 |
+-----------+

还有一个子查询，可以获取每个房间对的所有床位:

select
    r1.room room1,
        group_concat(distinct b1.bed_id order by b1.bed_id) beds1,
        r2.room room2,
        group_concat(distinct b2.bed_id order by b2.bed_id) beds2,
        least(count(distinct b1.bed_id),count(distinct b2.bed_id)) beds
    from rooms r1
    join rooms r2 on r2.room=concat(r1.room,'o')
    join beds b1 on b1.room_id=r1.room_id
    join beds b2 on b2.room_id=r2.room_id
    group by r1.room, r2.room

+-------+----------+-------+----------+------+
| room1 | beds1    | room2 | beds2    | beds |
+-------+----------+-------+----------+------+
| A101  | 1,2      | A101o | 7,8      |    2 |
| A102  | 3,4      | A102o | 9,10     |    2 |
| A103  | 5,6      | A103o | 11,12    |    2 |
| A205  | 13,14,15 | A205o | 16,17,18 |    3 |
+-------+----------+-------+----------+------+

将两者连接在一起，将 which_row 限制为每个房间对的床数:

select which_row, room1, beds1, room2, beds2
from (
    select
        r1.room room1,
        group_concat(distinct b1.bed_id order by b1.bed_id) beds1,
        r2.room room2,
        group_concat(distinct b2.bed_id order by b2.bed_id) beds2,
        least(count(distinct b1.bed_id),count(distinct b2.bed_id)) beds
    from rooms r1
    join rooms r2 on r2.room=concat(r1.room,'o')
    join beds b1 on b1.room_id=r1.room_id
    join beds b2 on b2.room_id=r2.room_id
    group by r1.room, r2.room
) room_pairs
join (
    select 1 which_row union all select 2 union all select 3 union all select 4
) which_row on which_row <= room_pairs.beds

+-----------+-------+----------+-------+----------+
| which_row | room1 | beds1    | room2 | beds2    |
+-----------+-------+----------+-------+----------+
|         1 | A101  | 1,2      | A101o | 7,8      |
|         2 | A101  | 1,2      | A101o | 7,8      |
|         1 | A102  | 3,4      | A102o | 9,10     |
|         2 | A102  | 3,4      | A102o | 9,10     |
|         1 | A103  | 5,6      | A103o | 11,12    |
|         2 | A103  | 5,6      | A103o | 11,12    |
|         1 | A205  | 13,14,15 | A205o | 16,17,18 |
|         2 | A205  | 13,14,15 | A205o | 16,17,18 |
|         3 | A205  | 13,14,15 | A205o | 16,17,18 |
+-----------+-------+----------+-------+----------+

然后只需更改所选字段即可从逗号分隔列表中为每一行获取正确的床位:

select
    room1,
    substring_index(substring_index(beds1, ',', which_row), ',', -1) bed1,
    room2,
    substring_index(substring_index(beds2, ',', which_row), ',', -1) bed2

+-------+------+-------+------+
| room1 | bed1 | room2 | bed2 |
+-------+------+-------+------+
| A101  | 1    | A101o | 7    |
| A101  | 2    | A101o | 8    |
| A102  | 3    | A102o | 9    |
| A102  | 4    | A102o | 10   |
| A103  | 5    | A103o | 11   |
| A103  | 6    | A103o | 12   |
| A205  | 13   | A205o | 16   |
| A205  | 14   | A205o | 17   |
| A205  | 15   | A205o | 18   |
+-------+------+-------+------+