我有这个查询：
SELECT
XMLELEMENT("row", XMLATTRIBUTES(productMain.variant as "order"),
(SELECT XMLELEMENT("attribute", XMLATTRIBUTES(product.id as "id") )
FROM product where product.variant = productMain.variant
)
)
FROM
(SELECT
DISTINCT product.variant
FROM
product
WHERE
product.fk_parent = 12345) productMain
而且我收到一个错误的“无效标识符productMain.variant”。
预期结果：
<row order="1">
<attribute name="Example1"/>
<attribute name="Example2"/>
...
</row>
<row order="2">
...
</row>
表product：
id | variant | name
_________________________
1 | 1 | Example1
2 | 1 | Example2
3 | 2 | Example3
4 | 3 | Example4
5 | 3 | Example5
...
SQLFIDDLE： http ://www.sqlfiddle.com/#!4 / e91a6 /2
我发现，Oracle没有将嵌套在一个以上层次的子查询关联起来。
我如何仍能得到想要的结果？
解决方案：
SELECT
XMLELEMENT("row", XMLATTRIBUTES(productMain.variant as "order"),
(
SELECT
XMLAGG(XMLELEMENT("attribute",XMLATTRIBUTES(product.name as "name"),product.variant)) FROM product
where product.fk_parent = 12345
and product.variant = productmain.variant
)
).getClobVal()
FROM
(SELECT
DISTINCT product.variant
FROM
product
WHERE
product.fk_parent = 12345) productMain;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句