时间戳

首页 > 解决方案 > Postgres 到 xml 排序依据

xml - Postgres 到 xml 排序依据

问题描述

我们正在执行以下 postgres 查询：

select
AT.id,
AT.goal,
SS.id,
SS.index,
SS.goal,
TK.id,
TK.index,
TK.label,
TK.actions,
TK.type,
TK.group,
TK.blind,
TK.dynamic
from
automations as SS,
automation_sets as AT,
tasks as TK
where
SS.automation_set = 321
AND SS.automation_set = AT.id
AND TK.automation = SS.id
order by
SS.index, TK.index

它返回以下数据：
数据提取

如何使用 Postgres xml 函数来聚合不同 XML 级别的数据，即第一级 AT.ID+AT.goal 和第二级 SS.ID + SS.goal？

编辑
我们设法获得了我们想要的信息，但仍有 1 个问题......使用的 SQL 如下：

SELECT XMLELEMENT(name automation_set,
    XMLATTRIBUTES(ASET_ID as id),
        XMLAGG(xml_task_group))
FROM (SELECT AT.id as ASET_ID,
        XMLELEMENT(name automation,
        XMLATTRIBUTES(SS.goal as id),
            XMLAGG(XMLELEMENT(name task,
            XMLATTRIBUTES(TK.id as task_id, TK.label, TK.actions, TK.type, TK.group, TK.blind, TK.dynamic, TK.index)))) as xml_task_group
from
    automations as SS,
    automation_sets as AT,
    tasks as TK
where
    SS.automation_set = 321
    AND SS.automation_set = AT.id
    AND TK.automation = SS.id
group by 
      AT.id, SS.id
order by 
      SS.index) t
group by t.ASET_ID

但是我们无法通过任务索引TK.index获取订单。我们必须确保它们的顺序正确，到目前为止我们还无法做到……

标签： xmlpostgresql

解决方案

完成，设法做到这一点...

我会贴出来供参考。

SELECT XMLELEMENT(name automation_set,
    XMLATTRIBUTES(ASET_ID as id),
        XMLAGG(xml_task_group))
FROM (SELECT AT.id as ASET_ID,
        XMLELEMENT(name automation,
        XMLATTRIBUTES(SS.goal as id),
            XMLAGG(XMLELEMENT(name task,
            XMLATTRIBUTES(TK.id as task_id, TK.label, TK.actions, TK.type, TK.group, TK.blind, TK.dynamic, TK.index)) order by TK.index)) as xml_task_group
from
    automations as SS,
    automation_sets as AT,
    tasks as TK
where
    SS.automation_set = 321
    AND SS.automation_set = AT.id
    AND TK.automation = SS.id
group by 
      AT.id, SS.id
order by 
      SS.index) t
group by t.ASET_ID

再次感谢你

推荐阅读