我正在尝试使用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>
更新：我修改了查询，只返回最新订单的所有元素。这个查询有点尴尬，因为源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>
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
}
