Вопрос:
Я как бы новичок в Oracle. Застрял в нижнем: у меня есть две таблицы:
Сайт:
**SiteID|SiteName** 1 Sydney 2 Newyork 3 Delhi
Люди:
**RecordID|PeopleID|SiteID** 1 1 1 2 1 2 3 2 2 4 3 1 5 3 2 6 3 3
Теперь в моем запросе я хочу получить что-то вроде этого:
**PeopleID | AssignedSites** 1 Sydney,NewYork 2 Newyork 3 Sydney,NewYork,Delhi
- Еще несколько пунктов:
-The решение должно работать в Oracle 10g, а также 11g.
-I дали небольшое подмножество данных в приведенном выше примере для краткости. Но в моем сценарии prod один человек может быть связан с 1000+ местами, и там мог 1000+ такой человек, поэтому решение не должно нарушать дело!
Любая помощь будет высоко оценена.
Заранее спасибо.
Лучший ответ:
Попробуйте использовать XMLAGG следующим образом:
select p.PeopleID, rtrim(xmlagg(xmlelement(e, s.SiteName, ‘,’)).extract(‘//text()’).getclobval(), ‘,’) from people p join site s on p.SiteID = s.SiteID group by p.PeopleID;
Если вам нужна конкатенация в определенном порядке, скажем, в порядок увеличения SiteId, затем добавить order by пункта в XMLAgg:
select p.PeopleID, rtrim(xmlagg(xmlelement(e, s.SiteName, ‘,’) order by s.SiteId).extract(‘//text()’).getclobval(), ‘,’) from people p join site s on p.SiteID = s.SiteID group by p.PeopleID;
РЕДАКТИРОВАТЬ:
Если вам нужен результат отображения для всех тех людей, которым присвоен сайт 100:
select p.PeopleID, rtrim(xmlagg( xmlelement(e, s.SiteName, ‘,’) order by s.SiteId ).extract(‘//text()’).getclobval(), ‘,’) from people p join site s on p.SiteID = s.SiteID join ( select distinct PeopleID from people where siteID = 1 ) p2 on p.PeopleID = p2.PeopleID group by p.PeopleID; Ответ №1
Это слишком долго для комментария.
listagg() является очевидным выбором, но он недоступен в Oracle 10. Однако даже в Oracle 11 listagg() ограничивается строками длиной 4000, и вы явно говорите: “Лицо может быть связано с местоположениями 1000+,
Есть способы обойти это, используя CLOB, XML и, без сомнения, другие решения. Однако, каково использование списка мест длиной в тысячи и тысячи символов? С таким количеством местоположений вы не сможете поместить результат в стандартное varchar2().
Возможно, подведение итогов в базе данных таким образом не является лучшим решением вашей реальной проблемы.
Ответ №2
Я думаю, что я близок к этому, просто нужна небольшая помощь: я создал функцию GetSiteName, которая возвращает имя сайта для SiteID. Теперь я использую ниже xmlagg, где мне нужно вызвать эту функцию GetSiteName:
select PeopleID, rtrim (xmlagg (xmlelement (e, clint.GetSiteName(SiteID) || ‘,’)).extract (‘//text()’), ‘,’) SITEIDS from client.People group by PeopleID;/
В основном нужна помощь в вызове функции изнутри xmlagg, каких-либо мыслей?
Ответ №3
Мне просто нужна была альтернатива для listagg в oracle 10g и нашла ее на этой странице https://oracle-base.com/articles/misc/string-aggregation-techniques.
просто используйте wm_concat, хотя он не поддерживается oracle и был сброшен в 12c.
с приведенным выше примером:
select p.PeopleID,wm_concat(s.SiteName) from people p join site s on p.SiteID = s.SiteID group by p.PeopleID;
