#oracle #join #rank
#Oracle #Присоединиться #ранжировать
Вопрос:
У меня есть 3 таблицы в базе данных Oracle с разной степенью актуальности в зависимости от статуса доставки.
В каждой таблице есть несколько столбцов, одинаковых по значению (но не по имени), и некоторые столбцы, специфичные для этой таблицы (например, размер, разделенный на 3 столбца == «длина», «ширина», «высота» ПРОТИВ одного столбца с «lengthXwidthXheight»).
Мне нужно иметь возможность выбирать определенные столбцы из каждой таблицы. Если в таблице нет этого столбца, в конечном результате может быть «НЕТ / пусто».
Table1:
orderid | name | table1 | table2 | ...
1 | a | ... | ... | ...
None | b | ... | ... | ...
2 | c | ... | ... | ...
Таблица 2:
orderid | name | table1 | count | ...
1 | a | ... | ... | ...
None | k | ... | ... | ...
4 | d | ... | ... | ...
Таблица 3:
orderid | name | table1 | place | ...
1 | a | ... | ... | ...
None | u | ... | ... | ...
4 | p | ... | ... | ...
7 | t | ... | ... | ...
Я хочу объединить все 3 таблицы по orderid в определенном порядке.
Приоритет: Таблица1> Таблица2 > Таблица3
- Выбираем все записи из Table1, где «orderid != None»
- Выбрать все записи из Таблицы2, где «orderid != None» и где «orderid» еще не существует из Таблицы1
- Выбрать все записи из Table3, где «orderid != None» и где «orderid» еще не существует, из Table1 или Table2
Таким образом, конечная таблица должна выглядеть следующим образом:
orderid | name | table1 | table2 |count | place
1 | a | ... | ... | ... | ...
2 | c | ... | ... | ... | ...
4 | d | ... | ... | ... | ...
7 | t | ... | ... | ... | ...
Как я могу этого добиться?
Ответ №1:
Вам нужно full outer joins здесь:
select
coalesce(t1.orderid,t3.orderid,t3.orderid) as coalesce_orderid,
coalesce(t1.name,t2.name,t3.name) as coalesce_name,
t1.name as name1,
t2.name as name2,
t3.name as name3
from
Table1 t1
full join Table2 t2
on t1.orderid=t2.orderid
full join Table3 t3
on nvl(t1.orderid,t2.orderid)=t3.orderid;
Если вы хотите исключить order=’None’, вы можете добавить
where coalesce(t1.orderid,t3.orderid,t3.orderid)!='None'
Полный пример с образцами данных:
with
Table1(orderid, name) as (
select '1' ,'a' from dual union all
select 'None' ,'b' from dual union all
select '2' ,'c' from dual
)
,Table2(orderid, name) as (
select '1' ,'a' from dual union all
select 'None' ,'k' from dual union all
select '4' ,'d' from dual
)
,Table3(orderid, name) as (
select '1' ,'a' from dual union all
select 'None' ,'u' from dual union all
select '4' ,'p' from dual union all
select '7' ,'t' from dual
)
select
coalesce(t1.orderid,t3.orderid,t3.orderid) as coalesce_orderid,
coalesce(t1.name,t2.name,t3.name) as coalesce_name,
t1.name as name1,
t2.name as name2,
t3.name as name3
from
Table1 t1
full join Table2 t2
on t1.orderid=t2.orderid
full join Table3 t3
on nvl(t1.orderid,t2.orderid)=t3.orderid;
Результаты:
COALESCE_ORDERID COALESCE_NAME NAME1 NAME2 NAME3
------------------ ------------------ ----- ----- -----
1 a a a a
None b b k u
4 d d p
2 c c
7 t t
Ответ №2:
То, что вы хотите, это UNION , а не a JOIN .
Вы можете использовать NOT EXISTS для проверки, являются ли строки в table2 или table3 дубликатами строк предыдущих таблиц с приоритетом:
select t1.* from table1 t1 where t1.orderid <> 'None'
union -- or union all
select t2.* from table2 t2
where t2.orderid <> 'None'
and not exists (select 1 from table1 t1 where t1.orderid = t2.orderid)
union -- or union all
select t3.* from table3 t3
where t3.orderid <> 'None'
and not exists (select 1 from table1 t1 where t1.orderid = t3.orderid)
and not exists (select 1 from table2 t2 where t2.orderid = t3.orderid)
И другой способ сделать это с ROW_NUMBER() функцией window:
select t.orderid, t.name, ....
from (
select u.*, row_number() over (partition by u.orderid order by u.tbl) rn
from (
select 1 tbl, t1.* from table1 t1 where t1.orderid <> 'None'
union all
select 2 tbl, t2.* from table2 t2 where t2.orderid <> 'None'
union all
select 3 tbl, t3.* from table3 t3 where t3.orderid <> 'None'
) u
) t
where t.rn = 1
Смотрите демонстрацию.
Результаты:
> orderid | name
> :------ | :---
> 1 | a
> 2 | c
> 4 | d
> 7 | t
Комментарии:
1. Спасибо за ваш ответ. Я забыл упомянуть, что некоторые столбцы в каждой таблице одинаковы (по крайней мере, по значению, а не по имени), а некоторые отличаются. Мне нужно выбрать несколько определенных столбцов из каждой таблицы (например, один имеет размер, разделенный на 3 столбца — длина, ширина, высота, у одного есть размер в одном столбце (lengthXwidthXheight). ОБЪЕДИНЕНИЕ выдает мне сообщение «в блоке запроса неверное количество столбцов результатов.
2. Затем вам нужно будет перечислить все столбцы вместо t1.* и создать поддельные столбцы как null вместо столбцов, которые отсутствуют в одной таблице, но существуют в других таблицах (и в правильном порядке). Другого способа получить результирующий набор, подобный тому, который вы хотите, нет.
3. Как я могу создать поддельные столбцы с нулевым значением?
4. Например, если столбец, подобный
columnonlyintable2, существует только в table2, и вы хотите, чтобы он отображался в результатах в формате 3d, то:select t1.orderid, t1.name, null columnonlyintable2, ... from table1 t1.... union all select t2.orderid, t2.name, t2.columnonlyintable2, ... from table2 t2 union all select t3.orderid, t3.name, null columnonlyintable2, ... from table3 t3....Устанавливаяnullв таблицах table1 и table3 вместо столбца, вы создаете поддельный столбец null. Это необходимо сделать для всех столбцов, которые не являются общими во всех 3 таблицах. Имя поддельного столбца будет выбрано из table1.