Как использовать LISTAGG для объединения нескольких строк?
У меня есть запрос отчета по этим строкам в APEX 5.0:
WITH inner_table AS
( select distinct
i.ID
,i.name
,i.lastname
,case i.gender
when 'm' then 'Male'
when 'f' then 'Female'
end gender
,i.username
,b.name region
,i.address
,i.city city
,i.EMAIL
,r.name as "ROLE"
,ie.address as "region_location"
,case
when i.gender='m' THEN 'blue'
when i.gender='f' THEN '#F6358A'
END i_color
,b.course as COURSE
,si.city UNIVERSITY
,case
when i.id in (select app_user from scholarship) then 'check'
else 'close'
end as scholarship,
case
when i.id in (select ieur.app_user from ie_user_role ieur where role=4) then 'Admin'
else ''
end admin,
apex_item.checkbox(10, i.id, 'UNCHECKED onclick = "highlightRow(this);"') as Del_usr
from app_users i left join regions b on (i.region=b.id)
left join ie_user_role ur on (i.id = ur.app_user)
left join ie_roles r on(ur.role = r.id)
left join user_house uh on (i.id=uh.app_user)
left join reg_location ie on (uh.house=ie.id)
left join study_list sl on i.id = sl.insan
left join study_institute si on sl.institute = si.id
left join course c on sl.course = c.id
where i.is_active='Y'
order by
i.name,i.lastname,i.username,region, city, i.EMAIL)
SELECT * FROM inner_table where (scholarship = :P5_SCHOLARSHIP or :P5_SCHOLARSHIP is null)
Я могу получить такие результаты:
|---------------------|------------------|-------|------------------|
| Name | Lastname | ... | Course |
|---------------------|------------------|-------|------------------|
| Some | User | ... | Course1 |
|---------------------|------------------|-------|------------------|
| Some | User | ... | Course2 |
|---------------------|------------------|-------|------------------|
Но я хотел бы получить зачисленные курсы в том же ряду, который повторялся ранее, поэтому:
|---------------------|------------------|-------|------------------|
| Name | Lastname | ... | Course |
|---------------------|------------------|-------|------------------|
| Some | User | ... | Course1, Course2 |
|---------------------|------------------|-------|------------------|
Я пробовал использовать LISTAGG и не записывал свои попытки, поэтому, к сожалению, я не могу опубликовать это сейчас. Я в основном пробовал:
,LISTAGG(b.course, ', ') within group (order by b.course) as COURSE
Затем добавляем GROUP BY с помощью COURSE, но в этом случае GROUP BY влияет на весь запрос, и я должен правильно применить другие столбцы, верно? В противном случае это приведет к "ORA-00937: не функция одногрупповой группы". Я немного заблудился там.
Еще я попробовал использовать таблицу подзапроса с той же строкой LISTAGG выше и получил желаемый результат из подзапроса, но затем присоединение к остальной части запроса не дало ожидаемых результатов.
Думаю, я мог бы использовать здесь небольшую помощь SQL для LISTAGG при объединении нескольких таблиц.
Спасибо.
Ответы 2
Когда вы используете агрегатную функцию (которая сворачивает несколько строк в одну), вам понадобится предложение GROUP BY, поэтому вам понадобится что-то вроде этого:
SELECT i.username,
LISTAGG( c.course, ', ' ) WITHIN GROUP ORDER BY ( c.course )
FROM app_users i
...
LEFT JOIN course c on sl.course = c.id
GROUP BY i.username
По сути, все, что не агрегируется, должно быть в пункте GROUP BY. Попробуйте использовать его в гораздо более простом запросе, пока не научитесь, а затем сделайте свой большой.
Ваше решение правильное, но теперь я вижу, что у меня могут быть некоторые ошибки в исходном запросе, не связанные со listagg, но это другая проблема ...
Вам нужен LISTAGG с аналитической оконной функцией. Затем удалите дубликаты с помощью отличного. Вот мой пример результата / данных: http://sqlfiddle.com/#!4/6e8e3f/3
Выберите DISTINCT name, last_name, другие столбцы, LISTAGG (курс, ',') ВНУТРИ ГРУППЫ (ПОКАЗАТЬ курс) НАД (РАЗДЕЛ ПО имени, фамилии) как "Курс" FROM inner_table;
Спасибо. И ваше решение, и решение из предыдущего ответа работают правильно в отношении моего исходного вопроса. В обоих случаях у меня есть 4 пользователя, курс которых повторяется в столбце LISTAGG дважды, например, «Course1, Course1», и это 100% указывает на ошибочный SQL в исходном запросе, я буду исследовать соответствующим образом.
За него проголосовали сейчас, ранее он сообщал, что я могу проголосовать, но он не отображается публично до тех пор, пока не появятся определенные очки репутации.
Дополнительный вопрос ... Как удалить дубликаты из LISTAGG? Например, у пользователя будут две разные «роли», так что это две строки для одного и того же пользователя. Я могу (и должен) поместить столбец ROLE под listagg, но он все равно будет выглядеть следующим образом: «someuser», «role1, role2», «course123, course123» и наоборот - «someuser», «role5, role5», "course2, course3".
Привет, это решает мои проблемы, связанные с неправильным использованием GROUP BY, да. Однако в случае всего запроса я не получаю правильных результатов. В исходном запросе было 694 записи, а при использовании LISTAGG - 691. При проверке, есть ли на 3 записи меньше, потому что они повторяются в курсе под одним и тем же пользователем (ами), как и ожидалось, я обнаружил, что тот же курс отображается дважды для 4 пользователей как listagg , т.е. 'Course1, Course1') - результат "ложный". В то время как для других 2 он отображается правильно, разные курсы для одного и того же пользователя указаны в одной строке с ','. Сейчас это сложно проверить.