oracle在xmlagg函数中获取distinct

kg7wmglp 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(383)

我需要在xmlagg函数中进行区分。例如下一个查询

select
       xmlagg(xmlelement(e, names.name || ' ')).extract('//text()').getclobval()
           within group (order by names.name ) names,
       xmlagg(xmlelement(e, actions.action || ' ')).extract('//text()').getclobval()
           within group ( order by actions.action) actions
from
json_table('[{"name": "Name1"}, {"name": "Name2"}, {"name": "Name3"}]', '$[*]'
                    columns (name varchar2 path '$.name')) names,
json_table('[{"action": "Call"}, {"action": "Write"}, {"action": "Write"}]', '$[*]'
                    columns (action varchar2 path '$.action')) actions

退货

NAMES                                                 | ACTIONS
-----------------------------------------------------------------------------------------------------------
Name1 Name1 Name1 Name2 Name2 Name2 Name3 Name3 Name3 | Call Write Write Call Write Write Call Write Write

我需要得到这样的结果

NAMES             | ACTIONS
-------------------------------
Name1 Name2 Name3 | Call Write

sql JSON xml oracle String

来源：https://stackoverflow.com/questions/63237490/oracle-get-distinct-in-xmlagg-function

1条答案

按热度按时间

u7up0aaq1#

我认为首先在子查询中选择不同的值比较简单：

select 
    (
        select xmlagg(xmlelement(e, name || ' ') order by name).extract('//text()').getclobval()
        from (
            select distinct name
            from json_table(
                '[{"name": "Name1"}, {"name": "Name2"}, {"name": "Name3"}]', '$[*]'
                columns (name varchar2 path '$.name')
            ) names
        ) n
    ) names,
    (
        select xmlagg(xmlelement(e, action || ' ') order by action).extract('//text()').getclobval()
        from (
            select distinct action
            from json_table(
                '[{"action": "Call"}, {"action": "Write"}, {"action": "Write"}]', '$[*]'
                columns (action varchar2 path '$.action')
            ) actions
        ) a
    ) actions
from dual

请注意 order by 的 xmlagg() 更好地适应功能。
db小提琴演示：

NAMES              | ACTIONS    
:----------------- | :----------
Name1 Name2 Name3  | Call Write

赞(0）回复(0）举报 2021-07-24

我来回答

oracle在xmlagg函数中获取distinct

1条答案

相关问题

热门标签

最新问答