时间戳

首页 > 解决方案 > 在间接相关表中的项目之间创建任意 1-1 对应关系

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

问题描述

考虑下表：

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);

标签： mysqlsql

解决方案

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

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   |
+-------+------+-------+------+

推荐阅读