如何将此SQL查询转换为等效的XQuery
SELECT e.essn AS one,f.essn AS two,e.pno,f.pno
FROM works_on AS e JOIN works_on AS f on e.pno = f.pno
AND e.essn < f.essn \n" . "ORDER BY `e`.`pno` ASC
到目前为止,我已经尝试过了,但是没有得到期望的结果
let $prods := doc("../company/works_on.xml")//works_on[pno = $project/pnumber]
for $d in distinct-values($prods/essn),$n in distinct-values($prods[essn = $d]/pno)
return <result essn="{$d}" pno="{$n}"/>
这是我正在使用的XML文件的示例。
<dataroot>
<works_on>
<essn>123456789</essn>
<pno>1</pno>
<hours>32.5</hours>
</works_on>
<works_on>
<essn>123456789</essn>
<pno>2</pno>
<hours>7.5</hours>
</works_on>
<works_on>
<essn>333445555</essn>
<pno>2</pno>
<hours>10</hours>
</works_on>
<works_on>
<essn>333445555</essn>
<pno>3</pno>
<hours>10</hours>
</works_on>
这是到目前为止的输出
<results>
<project>
<pnumber>1</pnumber>
<employee>
<essn>123456789</essn>
<essn>453453453</essn>
</employee>
</project>
<project>
<pnumber>2</pnumber>
<employee>
<essn>123456789</essn>
<essn>333445555</essn>
<essn>453453453</essn>
</employee>
</project>
<project>
<pnumber>3</pnumber>
<employee>
<essn>333445555</essn>
<essn>666884444</essn>
</employee>
</project>
<project>
<pnumber>10</pnumber>
<employee>
<essn>333445555</essn>
<essn>987987987</essn>
<essn>999887777</essn>
</employee>
</project>
<project>
<pnumber>20</pnumber>
<employee>
<essn>333445555</essn>
<essn>888665555</essn>
<essn>987654321</essn>
</employee>
</project>
<project>
<pnumber>30</pnumber>
<employee>
<essn>987654321</essn>
<essn>987987987</essn>
<essn>999887777</essn>
</employee>
</project>
</results>
我试图将essn的值分成两对,例如,在项目1上将保持不变,但对于项目2,将一对分别存在123456789和333445555,另一对将123456789和453453453,最后是333445555和453453453作为最后一对。它们应该是非重复且非反向的对。