带有 1 个 xml 文件的 xquery 中的 SUM 和 GROUP BY [英] SUM and GROUP BY in xquery with 1 xml file

问题描述

我有一个 SQL 查询:

I have an SQL query:

SELECT ShipVia, SUM(Freight)
FROM Orders
GROUP BY ShipVia

从访问数据库返回以下值

which returns the values below from the access database

Ship Via  TotalFreight
   1       $16,185.33
   2       $28,244.85
   3       $20,512.51

我正在尝试将其转换为 xquery(使用 xquery 1.0)

I am trying to convert this to xquery (using xquery 1.0)

到目前为止，我有这个，

So far I have this,

xquery version "1.0";
for $x in doc("Orders.xml")/dataroot/Orders
return
<OrderDetails>
{
    $x/ShipVia,
    <TotalFreight>{sum($x/Freight)}</TotalFreight>
}
</OrderDetails>

然而，这会像我一样输出每个订单以及运费

This however, outputs every single order along with the freight cost as if I did

SELECT ShipVia, Freight
FROM Orders

在 SQL 中

如何让 xquery 像 SQL 命令一样实际添加每一项

How do I make the xquery actually add each one up as the SQL command does

例如，这是来自 Orders.xml 文件的三个订单

These are three orders from the Orders.xml file for example

<dataroot>
    <Orders>
        <ShipVia>1</ShipVia>
        <Freight>32.38</Freight>
    </Orders>
    <Orders>
        <ShipVia>1</ShipVia>
        <Freight>11.61</Freight>
    </Orders>
    <Orders>
        <ShipVia>2</ShipVia>
        <Freight>65.83</Freight>
    </Orders>
</dataroot>

去除不必要的节点(添加重复)

Stripped Unnecessary Nodes (Added Duplicate)

推荐答案

以下是在 XQuery 1.0(缺少 XQuery 3.0 的原生 group by 运算符)中执行此操作的一种方法:>

The following is one approach to doing this in XQuery 1.0 (which lacks XQuery 3.0's native group by operator):

let $doc := 
    <dataroot>
        <Orders>
            <ShipVia>1</ShipVia>
            <Freight>32.38</Freight>
        </Orders>
        <Orders>
            <ShipVia>1</ShipVia>
            <Freight>11.61</Freight>
        </Orders>
        <Orders>
            <ShipVia>2</ShipVia>
            <Freight>65.83</Freight>
        </Orders>
    </dataroot>

let $ship_via_values := distinct-values($doc/Orders/ShipVia/text())
for $ship_via_value in $ship_via_values
return
  <OrderDetails>
    <ShipVia>{$ship_via_value}</ShipVia>
    <TotalFreight>{
      sum($doc/Orders[ShipVia=$ship_via_value]/Freight)
    }</TotalFreight>
  </OrderDetails>

此查询返回以下结果:

<OrderDetails>
  <ShipVia>1</ShipVia>
  <TotalFreight>43.99</TotalFreight>
</OrderDetails>
<OrderDetails>
  <ShipVia>2</ShipVia>
  <TotalFreight>65.83</TotalFreight>
</OrderDetails>

...这似乎是所需的输出.

...which appears to be desired output.

这篇关于带有 1 个 xml 文件的 xquery 中的 SUM 和 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！