SQL Server中的FLWOR命中数 [英] FLWOR in Sql server count number of hits
问题描述
我正在使用SQL Server 2008 R2.我的问题是我想计算使用FLWOR从XQuery查询中收到的点击数.对于每个匹配,我想要一个连续的数字,例如:0、1、2、3、4 ...
I am using SQL Server 2008 R2. My problem is that I want to count number of hits that i receive from XQuery query using FLWOR. For each hit, I want a consecutive number, like: 0,1,2,3,4...
我的查询:
select @xml.query('for $s at $count in /Root/Persons/Person
return <Person ID="{$count}">{$s}</Person>')
这里唯一的问题是SQL Server不支持此操作,并且我收到错误消息:
The only problem here is this is not supported in SQL Server and I receive an error:
Msg 9335, Level 16, State 1, Line 16
XQuery [query()]: The XQuery syntax 'at' is not supported.
我也尝试过使用let关键字并定义新变量,但是我不知道如何在每次迭代中增加该变量的值?
I've also tried with let keyword and define new variable but I don't know how to increase value of that variable with each iteration?
感谢所有答案,Frenky
Thanks for all the answers, Frenky
推荐答案
XQuery是一种声明性语言,您不能使用let
来递增计数器.
XQuery is a declarative language, you cannot use let
to increment a counter.
缺少的at
功能的一种颇为骇人的解决方法是计算前面的同级<person/>
标签:
A rather hackish workaround to the missing at
feature would be to count the preceding sibling <person/>
tags:
for $person in /Root/Persons/Person
let $count := count($person/preceding-sibling::Person) + 1
return <Person ID="{$count}">{$person}</Person>
请注意,由于重复进行先前的同级扫描,如果执行引擎未对其进行优化,则此代码将具有O(n^2)
运行时.
Be aware that this code will have O(n^2)
runtime if not optimized by the execution engine (which it will probably not do) because of the repeated preceding sibling scans.
编辑:如注释中所述,MS SQL甚至不支持preceding-sibling
轴.它们确实支持 <<
节点顺序比较运算符.此查询应得到完全支持:
Edit: As stated in the comments, MS SQL doesn't even support the preceding-sibling
axis. They do support the <<
node order comparison operator, though. This query should be fully supported:
for $person in /Root/Persons/Person
let $count := count($person/parent::Persons/Person[. << $person]) + 1
return <Person ID="{$count}">{$person}</Person>
顺便说一句,您可能只想粘贴此人的名字,所以更好地使用
By the way, you possibly only want to paste the person's name, so better use
(: snip :)
return <Person ID="{$count}">{data($person)}</Person>
这篇关于SQL Server中的FLWOR命中数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!