Блог

Главная - Вопросы по программированию - Есть ли какой-либо способ использовать DISTINCT в функции xmlagg в sql?

Есть ли какой-либо способ использовать DISTINCT в функции xmlagg в sql?

#sql #oracle #oracle11g

#sql #Oracle #oracle11g

Вопрос:

Я использовал следующую команду.

 select col1, col2, XMLAGG(XMLELEMENT(E, colname || ',')).EXTRACT('//text()')
from table
group by col1, col2

вывод:

 col1      col2      col3

KOCHI   ERNAKULAM   residential, commercial, residential, residential

Но мне нужен следующий вывод как

 col3 :

residential, commercial.

Я попытался использовать DISTINCT в подзапросе, но не получил требуемого результата. Любая помощь?

Комментарии:

1. Да. Oracle Database 11g Enterprise Edition Выпуск 11.2.0.3.0

Ответ №1:

как насчет использования regexp_replace функции? например

 select
col1,
col2,
regexp_replace(XMLAGG(XMLELEMENT(E, colname || ',')).EXTRACT('//text()'),'([^,] )(,1) ', '1') as colname_list
from table
group by col1, col2

Ответ №2:

Вы можете использовать distinct в подзапросе следующим образом:

 select col1, col2, XMLAGG(XMLELEMENT(E, colname || ',')).EXTRACT('//text()')
from (select distinct col1, col2, colname from table)
group by col1, col2

Ответ №3:

Сначала выберите различные значения, затем примените XMLAGG , например

 select col1, col2, XMLAGG(XMLELEMENT(E, colname || ',')).EXTRACT('//text()')
from (select distinct col1, col2, colname
      from table 
     )
group by col1, col2

Хотя, если результирующая строка не превышает 4000 символов, рассмотрите возможность использования LISTAGG :

 select col1, col2, LISTAGG(colname, ',')) within group (order by null)
from (select distinct col1, col2, colname
      from table 
     )
group by col1, col2

Ответ №4:

Хотя DISTINCT в этом случае работает подзапрос with , это не обобщаемое решение. Это может мешать другим столбцам, которые вы, возможно, захотите включить в запрос.

XMLAGG() игнорирует NULL значения. Итак, более общий подход использует ROW_NUMBER() :

 select col1, col2,
       XMLAGG(CASE WHEN SEQNUM. = 1 THEN XMLELEMENT(E, colname || ',') END).EXTRACT('//text()')
from (select t.*,
             ROW_NUMBER() OVER (PARTITION BY col1, col2, colname ORDER BY colname) as seqnum
      from t
     ) t
group by col1, col2
Метки: