Блог

С помощью этой схемы:

 CREATE TABLE BAR (id INT PRIMARY KEY);
CREATE TABLE FOO (id INT PRIMARY KEY, rank INT UNIQUE, fk INT, FOREIGN KEY (fk) REFERENCES Bar(id));

INSERT INTO BAR (id) VALUES (1);
INSERT INTO BAR (id) VALUES (2);

-- sample values
INSERT INTO FOO (id, rank, fk) VALUES (1, 10, 1);
INSERT INTO FOO (id, rank, fk) VALUES (2, 3, 1);
INSERT INTO FOO (id, rank, fk) VALUES (3, 9, 2);
INSERT INTO FOO (id, rank, fk) VALUES (4, 5, 1);
INSERT INTO FOO (id, rank, fk) VALUES (5, 12, 1);
INSERT INTO FOO (id, rank, fk) VALUES (6, 14, 2);
  

Как я могу запросить определенные строки FOO и строки, связанные с той же строкой BAR со следующим по старшинству rank значением? То есть я хочу выполнить поиск по определенным строкам («targets»), и для каждой целевой строки я также хочу найти другую строку («secondary»), такую, чтобы у secondary был самый высокий ранг из всех строк с secondary.fk = target.fk и secondary.rank < target.rank .

Например, если я нацелен на все строки (без предложения where), я бы ожидал такого результата:

 TARGET_ID  TARGET_RANK  SECONDARY_ID  SECONDARY_RANK
---------  -----------  ------------  --------------
        1           10             4               5
        2            3          NULL            NULL
        3            9          NULL            NULL
        4            5             2               3
        5           12             1              10
        6           14             3               9
  

Когда целевая строка имеет id 2 или 3, вторичной строки нет, потому что ни одна строка не совпадает fk с целевой строкой и имеет меньшую rank .

Я попробовал это:

 SELECT F1.id AS TARGET_ID, F1.rank as TARGET_RANK, F2.id AS SECONDARY_ID, F2.rank AS SECONDARY_RANK
FROM FOO F1
LEFT JOIN FOO F2 ON F2.rank = (SELECT MAX(S.rank)
                               FROM FOO S
                               WHERE S.fk = F1.fk
                                 AND S.rank < F1.rank);
  

…но я получил ORA-01799: a column may not be outer-joined to a subquery .

Затем я попробовал это:

 SELECT F1.id AS TARGET_ID, F1.rank AS TARGET_RANK, F2.id AS SECONDARY_ID, F2.rank AS SECONDARY_RANK
FROM FOO F1
LEFT JOIN (SELECT S1.rank, S1.fk
           FROM FOO S1
           WHERE S1.rank = (SELECT MAX(S2.rank)
                            FROM FOO S2
                            WHERE S2.rank < F1.rank
                              AND S2.fk = F1.fk)
           ) F2 ON F2.fk = F1.fk;
  

…но я получил ORA-00904: "F1"."FK": invalid identifier .

Наверняка есть какой-то способ сделать это в одном запросе?

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

 select F1.id as TARGET_ID, F1.rank as TARGET_RANK, F2.id as SECOND_ID, F2.rank as SECOND_RANK
from FOO F1
left join FOO F2 on F1.fk = F2.fk and F2.rank < F1.rank
where F2.rank is null or F2.rank = (select max(S.rank)
                                    from FOO S
                                    where S.fk = F1.fk
                                      and S.rank < F1.rank);
  

 select f1.rank f1rank, f2.rank as f2rank
from foo f1
left join foo f2 on f1.fk = f2.fk and f1.rank > f2.rank
  

Это вернет строку для каждой записи foo, которая имеет другую запись в той же таблице (используя fk для определения этого) и ниже существующего ранга. Теперь вам просто нужно максимальное значение (f2.rank) оттуда. Перейдите выше в подзапрос и найдите максимальное

 Select f1rank, max(f2rank) f2rank
from
(select f1.rank f1rank, f2.rank as f2rank
from foo f1
left join foo f2 on f1.fk = f2.fk and f1.rank > f2.rank) a
group by f1rank
  

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

Вам нужны аналитические функции

 select id, 
lead(rank) over (partition by fk order by rank) next_rank
lag(rank) over (partition by fk order by rank)  prev_rank
from foo
  

Это более эффективно, чем самостоятельные соединения.

Но если вы хотите подвергнуть пыткам свою базу данных, вы можете попробовать

 select id, 
(select min(f2.rank) from foo f2 where f2.fk = f1.fk and f2.rank >f1.rank) next_rank,
(select max(f2.rank) from foo f2 where f2.fk = f1.fk and f2.rank <f1.rank) prev_rank
from foo f1
  

Хорошо, я неправильно понял, что должно быть в выходных данных. Вот пример:

 select id, rank, prev_rank_id from (
select id, rank,
lag(id) over (partition by fk order by rank) prev_rank_id
from foo
)
where id in (1, 3)
  

Аналитические функции работают с ВЫХОДНЫМ набором данных. Вам нужно обернуть это в другой оператор select, чтобы ограничить ваш фактический вывод.