每个客户的XQuery最新订单

Question

我正在尝试使用Altova XMLSpy和XQuery1.0为每个客户返回最近的订单。

在SQL中,查询如下所示：

SELECT `Order ID`, `Customer ID`, `Employee ID`, `Order Date`
FROM Orders AS O1
WHERE `Order Date` =
  (SELECT MAX(`Order Date`)
   FROM Orders AS O2
   WHERE O2.[Customer ID] = O1.[Customer ID]);

它返回16行，但我无法获得类似于XQuery中的工作。

我尝试了多种不同的代码，我认为我得到的最接近的代码是：

<result>
{
    for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
    return
    <Customer>
    {
        for $order in doc("Orders.xml")//Orders
        where $cust = $order/Customer_x0020_ID
        return max(xs:string($order/Order_x0020_Date))

    }
    </Customer>

}
</result>

对于XML从MS Access导出的糟糕的标记名表示歉意。

请救命！提前道谢。

<Orders>
  <Order_x0020_ID>30</Order_x0020_ID>
  <Employee_x0020_ID>9</Employee_x0020_ID>
  <Customer_x0020_ID>27</Customer_x0020_ID>
  <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Orders>

编辑：在尝试JoeMFB的解决方案后，当我只需要最近的（或最大日期）时，我会收到每个客户的所有订单：

<Customer>
    <Order_x0020_ID>57</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>30</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Customer>
<Customer>
    <Order_x0020_ID>80</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>2</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-25T17:03:55</Order_x0020_Date>
    <Order_x0020_ID>58</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>61</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-07T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>34</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-02-06T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>31</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-20T00:00:00</Order_x0020_Date>
</Customer>

Answer 1

XQuery3.0解决方案。

像saxon：hight（）这样的高阶函数在这里可能很有用：请参阅

http://www.saxonica.com/documentation/#！functions/saxon/hight

使用这样的函数，代码会变成如下所示（我假设示例数据中的“orders”元素表示一个订单，并重复使用）：

for $o in //Orders
group by $o/Customer_ID
return saxon:highest($o, function($order){xs:date($order/Order_Date)})

如果不使用Saxon，可以自己编写Saxon：hight（）函数，如下所示：

declare function saxon:highest($s as item()*, $f as function(item()*) as xs:anyAtomicValue) as item()?{
  if (count($s) lt 2)
  then head($s)
  else (
      let $h := saxon:highest(tail($s), $f)
      return if ($f(head($s)) gt $f($h))
             then head($s)
             else $h
}

Answer 2

更新：我修改了查询，只返回最新订单的所有元素。这个查询有点尴尬，因为源XML没有按顺序对元素进行分组。

<result>
{
  for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
  return
    <Customer>
    {
      let $date :=
      (
        for $cid in doc("Orders.xml")//Orders/Customer_x0020_ID[. eq $cust]
        let $date := $cid/following-sibling::Order_x0020_Date[1]
        order by xs:dateTime($date) descending
        return $date
      )[1]
      return
      (
        $date/preceding-sibling::Order_x0020_ID[1],
        $date/preceding-sibling::Customer_x0020_ID[1],
        $date/preceding-sibling::Employee_x0020_ID[1],
        $date
      )
    }
    </Customer>
}
</result>