Блог

У меня есть таблица с несколькими людьми в одном номере, которую мне нужно сопоставить в номере, а затем отобразить каждого человека в качестве первого идентификатора только один раз с их списком соседей по комнате.

Поскольку их больше, чем просто два, я сталкиваюсь со сценариями, в которых у меня есть несколько результатов, где первый идентификатор указан несколько раз, а соседи по комнате упорядочены по-разному, или я могу вернуть только одну строку с 4 идентификаторами

Пример:

У меня есть такой стол, как этот:

 Table Name: ROOM_LIST
  

 ID | BUILDING | SUITE | ROOM
01 | BU_1     | SU_1  | RO_1
02 | BU_1     | SU_1  | RO_2
03 | BU_1     | SU_1  | RO_3
04 | BU_1     | SU_1  | RO_4
05 | BU_1     | SU_2  | RO_1
06 | BU_1     | SU_2  | RO_2
07 | BU_2     | SU_1  | RO_1
08 | BU_2     | SU_1  | RO_2
  

Я попробовал сделать запрос, подобный этому:

 select A.ID as Primary,
       B.ID as Roomate_1,
       C.ID as Roomate_2,
       D.ID as Roomate_3,
       A.BUILDING as Building,
       A.SUITE As Suite,
       A.ROOM As Room
from ROOM_LIST A
Left Join ROOM_LIST B on A.BUILDING = B.BUILDING and A.SUITE = B.SUITE
Left Join ROOM_LIST C on A.BUILDING = C.BUILDING and A.SUITE = C.SUITE
Left Join ROOM_LIST D on A.BUILDING = D.BUILDING and A.SUITE = D.SUITE
where A.ID > B.ID
and   A.ID > C.ID
and   A.ID > D.ID
and   B.ID > C.ID
and   B.ID > D.ID
and   C.ID > D.ID
order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room;
  

Это избавляет от лишних дубликатов, но я получаю только одну строку на набор вместо одной строки с каждым идентификатором в качестве основного.

Также попробовал аналогичный, но с <> или != вместо >, а затем я получаю несколько дубликатов с одним идентификатором в качестве первого, но 2-й, 3-й и 4-й меняются местами, поэтому технически они не являются дубликатами.

Вот почему я говорю «неидентичные» дубликаты 🙂

Мои конечные результаты будут выглядеть примерно так:

 | Primary | Roomate_1 | Roomate_2 | Roomate_3 | Building | Suite | Room
| 01      | 02        | 03        | 04        | BU_1     | SU_1  | RO_1
| 02      | 03        | 04        | 01        | BU_1     | SU_1  | RO_2
| 03      | 04        | 01        | 02        | BU_1     | SU_1  | RO_3
| 04      | 01        | 02        | 03        | BU_1     | SU_1  | RO_4
| 05      | 06        | Null      | Null      | BU_1     | SU_2  | RO_1
| 06      | 05        | Null      | Null      | BU_1     | SU_2  | RO_2
  

Я пробовал несколько запросов с разным вложенным выбором в инструкции select или в from, но, похоже, я не могу свести это к одному результату для идентификатора в качестве основного. Я изучил возможность создания сводной таблицы, но (в моем понимании) это сработало бы, только если бы у меня был один и тот же идентификатор, имеющий несколько результатов, и я хотел превратить несколько результатов в столбцы.

Думал об объединении, но я не знаю, как выполнить запрос через объединения? если это вообще возможно

Любая помощь будет оценена

РЕДАКТИРОВАТЬ: приведенное ниже решение работает только в 12C, но мне нужно решение 11G:

   with dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 id , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 id , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04 id , 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 id , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 id , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 id , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 id , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual )
    SELECT  
    A.ID as Primary,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=1)  Roomate_1,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=2)  Roomate_2,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite and b.ID != a.ID  order by id ) where rn=3)  Roomate_3,
           a.BUILDING as Building,
           A.SUITE As Suite,
           A.ROOM As Room
    FROM
        dt a 
    order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room  
  

Я добавил следующее к одному из приведенных ответов: and b.ID != a.ID и изменил rn=2 to rn=1 , чтобы начать подсчет из-за 0

Не уверен в влиянии на производительность. Потребуется выполнить анализ, но результат будет таким, как ожидалось.

Ответ 12c.

     with dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04, 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual )
    SELECT  
    A.ID as Primary,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=2)  Roomate_1,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=3)  Roomate_2,
            ( select id from (select id,rownum rn from dt  b where  a.building = b.building AND a.suite = b.suite  order by id ) where rn=4)  Roomate_3,
           a.BUILDING as Building,
           A.SUITE As Suite,
           A.ROOM As Room
    FROM
        dt a 
    order by Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room                            
  

ответ 11g. Я не уверен, какой порядок данных в столбце roomate1 в roomate3 необходим.

 WITH  dt as (
    select 01 id , 'BU_1' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 02 id , 'BU_1' building,  'SU_1' suite ,'RO_2' room from dual union all 
    select 03 id , 'BU_1' building,  'SU_1' suite ,'RO_3' room from dual union all 
    select 04 id , 'BU_1' building,  'SU_1' suite ,'RO_4' room from dual union all 
    select 05 id , 'BU_1' building,  'SU_2' suite ,'RO_1' room from dual union all 
    select 06 id , 'BU_1' building,  'SU_2' suite ,'RO_2' room from dual union all 
    select 07 id , 'BU_2' building,  'SU_1' suite ,'RO_1' room from dual union all 
    select 08 id , 'BU_2' building,  'SU_1' suite ,'RO_2' room from dual ),
joindrslt AS (
    SELECT a.*, b.id roommate,
        ROW_NUMBER() OVER(PARTITION BY a.suite, a.building, a.room ORDER BY b.id ) AS ri
    FROM 
    dt a 
    JOIN dt b ON a.building = b.building AND a.suite = b.suite AND b.id != a.id
    ORDER BY b.id
)
SELECT ID Primary,
            roomate_1,
            roomate_2,
            roomate_3,
            Building,Suite,
            Room  FROM
    (
        SELECT
            * 
        FROM
            joindrslt PIVOT (
                MAX ( roommate )
                FOR ri
                IN ( 1 AS roomate_1, 2 AS roomate_2, 3 AS roomate_3 )
            )
    )
ORDER BY
 Primary,Roomate_1,Roomate_2,Roomate_3,Building,Suite,Room