有关在SQL Server中使用XQuery进行排序的问题

问题描述

SELECT的结果是否像下面在SQL Server中使用XQuery的结果一样保证以文档顺序显示，即节点以原始顺序显示在xml字符串中?

Is the result of a SELECT like the one below using XQuery in SQL Server guaranteed to be in document order, that is in the original order the nodes are listed in the xml string?

DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>'
SELECT t.i.value('.', 'int')
FROM @x.nodes('/hey/@i') t(i)

我之所以问，是因为通常SELECT语句不能保证订单，除非提供了订单.

I ask because in general SELECT statements do not guarantee an order unless one is provided.

此外，如果可以保证(或不保证)此订单，那么是否可以在Microsoft网站上正式记录该订单?

Also, if this order is (or is not) guaranteed, is that documented somewhere officially, maybe on Microsoft's web site?

最后，是否可以在SELECT语句中根据原始文档顺序对文档顺序进行排序或进行其他奇怪的排序和查询?

Last, is it possible to sort opposite of document order or do other strange sorts and queries based on the original document order from within the SELECT statement?

推荐答案

DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>'是XML序列"的示例.根据定义，没有排序顺序，选择应该始终按文档原始顺序返回.

TheDECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>'is an example of an XML "sequence". By definition, without a sort order, the select should always come back in the documents original order.

如前所述，您可以更改排序顺序.这是一个示例:

As already mentioned, you can change the sort order. Here is one example:

选择t.i.value('.'，'int')作为x，ROW_NUMBER()OVER(ORDER BY(SELECT NULL))作为rn

SELECT t.i.value('.', 'int') as x, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn

FROM @ x.nodes('/hey/@ i')t(i)

FROM @x.nodes('/hey/@i') t(i)

按rn desc排序

以下是Microsoft网站上有关序列的一些信息:

Here is some information about sequences on the Microsoft site:

http://msdn.microsoft.com/en-us/library/ms179215(v=sql.105).aspx

这是有关Xquery中序列的更一般的讨论.

Here is a more general discussion of a sequence in Xquery.

http://en.wikibooks.org/wiki/XQuery/Sequences

在阅读我上面提到的Microsoft网站上的页面后，我意识到上面的原始答案不正确.该页面说您需要在元素之间使用逗号来构造序列.给出的示例不是序列".但是，关于更改排序顺序的原始信息仍然是:)

I realize that my original answer above is incorrect after reading the page on the Microsoft site I referred to above. That page says that you need a comma between elements to construct a sequence. The example given is not a "sequence". However, my original info about changing the sort order stands :)

这篇关于有关在SQL Server中使用XQuery进行排序的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！