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屋！