mysql - 每个客户的 XQuery 最近订单

Question

我正在尝试使用 Altova XMLSpy 和 XQuery 1.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>

对于从 MS Access 导出的 XML 中糟糕的标签名称，我们深表歉意。

求助!提前致谢。

<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

更新:我修改了查询以仅返回最新订单的所有元素。这个查询有点笨拙，因为您的源 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>