Переписать запрос в ЦИКЛЕ FOR в один запрос

#sql #oracle #loops #for-loop #subquery

#sql #Oracle #циклы #for-цикл #подзапрос

Вопрос:

Не могли бы вы, пожалуйста, помочь мне переписать следующий запрос внутри запроса цикла FOR в один запрос без цикла:

 for rec in select distinct sp.student_id, v.test_id, v.name, p.version_id from student_pages sp, pages p, versions v where p.id = sp.page_id and v.id = p.version_id order by student_id, test_id, name LOOP          
      select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index) as lost_page_indices into l_lost_page_indices from pages p left join student_pages sp on p.id = sp.page_id and sp.student_id = rec.student_id where p.version_id = rec.version_id and sp.page_id is null;     
end loop;

В последнем запросе мне нужны следующие поля: sp.student_id, v.test_id, v.name и lost_page_indices.

1. string_agg() В Oracle нет

2. Вау, это какой-то запущенный оператор, который у вас есть. Что с предложениями FROM и WHERE после into l_lost_page_indices ? Было бы лучше начать сначала: описать три базовые таблицы (`student_pages и т. Д.) — Показать имена столбцов и типы данных (ОК, чтобы включать только соответствующие столбцы), обозначения первичного и внешнего ключей, а не ограничения null — и затем, что вы пытаетесь сделать. Объясните на английском языке, предоставьте небольшую выборку входных данных и желаемый результат; не нужно пытаться поместить это в запрос, мы можем помочь с этим.

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

Ответ №1:

Первое сокращение: возьмите запрос, который вы перебираете, превратите его в подзапрос и используйте его как объединенную таблицу.

 select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index)
       as lost_page_indices into l_lost_page_indices
from pages p
join (
     select distinct sp.student_id, v.test_id, v.name, p.version_id
     from student_pages sp
     join pages p on p.id = sp.page_id
     join versions v on v.id = p.version_id
) as rec on rec.student_id = sp.student_id
left join student_pages sp on p.id = sp.page_id and
                              sp.student_id = rec.student_id
where p.version_id = rec.version_id and
      sp.page_id is null
order by rec.student_id, rec.test_id, rec.name

Я реорганизовал подзапрос, используя синтаксис соединения для упрощения чтения.

order by нельзя полагаться на то, что соединение сохранится, поэтому оно перемещается во внешний запрос.

Нет group by , поэтому я не уверен, будет ли работать агрегатная функция.

И, как указывали другие, string_agg() не является встроенной функцией Oracle. Вы должны сделать это самостоятельно.

Это можно упростить, между двумя объединениями много избыточности. Этот подзапрос объединяет student_pages, страницы и версии, которые могут быть выполнены с помощью обычного соединения. Единственное, что осталось distinct sp.student_id , это то, что можно лучше сделать с помощью a group by sp.student_id .

 select STRING_AGG(cast(p.index as varchar), ';' ORDER BY p.index)
      as lost_page_indices into l_lost_page_indices
from pages p
left join student_pages sp on sp.page_id = p.id
join versions v on v.id = p.version_id
where sp.page_id is null
group by sp.student_id
order by sp.student_id, v.test_id, v.name

Я не уверен на 100%, что это эквивалентный запрос, но он должен помочь вам начать. Это делает намного понятнее, что делает запрос: найдите потерянные страницы и вставьте их в таблицу.