文章/答案/技术大牛

发布

社区首页 >问答首页 >在间接关联表中的项之间创建任意的1-1对应关系

问在间接关联表中的项之间创建任意的1-1对应关系
EN

Stack Overflow用户

提问于 2020-09-03 10:52:07

回答 2查看 55关注 0票数 2

请考虑下表：

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有三张床，A102o也会有三张床，以此类推。

这很接近，但给了我重复的内容：

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

mysql

sql

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-09-03 12:06:38

如果您只想为每对相关房间安排两行：

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

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

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

票数 1

Stack Overflow用户

发布于 2020-09-03 20:48:09

基本上，您希望将房间配对在一起，然后为它们分配床。以下是一种方法：

with br as (
      select b.*, r.room, r.pair,
             row_number() over (partition by r.room order by b.id) as seqnum,
             dense_rank() over (partition by pair order by r.room) as pair_seqnum
      from beds b join
           (select r.*,
                   floor( (row_number() over (order by rand()) - 1) % (count(*) over () / 2)) as pair
            from rooms r
           ) r
           on b.roomid = r.id
     )
select min(case when pair_seqnum = 1 then room end) as room_1,
       min(case when pair_seqnum = 1 then id end) as bed_1,
       min(case when pair_seqnum = 2 then room end) as room_2,
       min(case when pair_seqnum = 2 then id end) as bed_2
from br
group by pair, seqnum;

最里面的子查询为房间分配一个配对编号。这只是使用算术为每个房间生成一个从0到2的数字。

然后，CTE列举了房间和床位。

外部查询聚合。

最初，我以为你想要一个随机配对，所以我也会包含这个逻辑：

select min(case when b.bedno = 1 then b.id end) as bed_1,
       min(case when b.bedno = 1 then r.room end) as room_1,
       min(case when b.bedno = 2 then b.id end) as bed_2,
       min(case when b.bedno = 2 then r.room end) as room_2
from (select b.*,
             row_number() over (partition by bedno order by rand()) as pair
      from (select b.*,
                   row_number() over (partition by roomid order by rand()) as bedno
            from beds b
           ) b
     ) b join
     rooms r
     on b.roomid = r.id
group by pair;

Here是一个db<>fiddle。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/63716091

复制

相似问题

问在间接关联表中的项之间创建任意的1-1对应关系
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在间接关联表中的项之间创建任意的1-1对应关系EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在间接关联表中的项之间创建任意的1-1对应关系
EN