SQL Server 中的递归结果集排序
我在构建返回 XML 样式层次结构的查询时遇到了极大的困难。
我们有一个数据库表，其中包含我们网站的 URL 层次结构。该表包含以下列：ID、URL、DisplayName、ParentID、ItemOrder
父 ID 在当前项与其父项之间形成递归关系。该项目应位于层次结构中其父级的下方，并且还应使用项目顺序针对层次结构中同一级别的项目进行排序。
我已经设法使递归查询正常工作，因此它可以按顺序向下钻取层次结构，但我也无法按项目顺序对其进行排序。
我当前的查询如下：
WITH Parents AS
(
SELECT MenuItemId, URL, ParentItemId, ItemOrder
FROM CambsMenu
UNION ALL
SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)
SELECT DISTINCT *
FROM Parents
I am having extreme difficulty constructing a query which returns an XML style hierarchy.
We have a database table which contains a hierarchy of URLs for our website. The table contains the columns: ID, URL, DisplayName, ParentID, ItemOrder
The parent ID forms a recursive relationship between the current item and it's parent. The item should site below it's parent in the hierarchy and it should also be ordered using the item order against items at the same level in the hierarchy.
I have managed to get a recursive query working so it drills down the hierarchy sequentially but I cannot order this by the item order as well.
My current query is below:
WITH Parents AS
(
SELECT MenuItemId, URL, ParentItemId, ItemOrder
FROM CambsMenu
UNION ALL
SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)
SELECT DISTINCT *
FROM Parents
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
正常的分层方法：
查询：
输出：
现在让我们覆盖组项目经理的排序，让项目经理 2 位于项目经理 1 之前，项目经理 1 位于项目经理 2 之后。让我们还让测试人员 4 位于 3 之前，测试人员 3 位于之后tester 4
查询：
输出：
在数据投影中没有排序列：
输出：
项目经理 2 出现在项目经理 1 之前。 测试人员 4 出现在测试人员 3 之前
该技术在于如果存在 order_override(non-null)，则用数字文本替换 b.name ):
上面的代码是Postgres，要转换成Sql Server，去掉
RECURSIVE字样，将REPEAT改为REPLICATE，||< /code> 到+。相当于...
...是：
Normal hierachical approach:
Query:
Output:
Now let's override sorting on Group Project Managers, let's make Project Manager 2 come before 1, and Project Manager 1 come after Project Manager 2. Let's also make tester 4 comes before 3, and tester 3 comes after tester 4
Query:
Output:
Without the sort column in data projection:
Output:
Project Manager 2 comes before Project Manager 1. Tester 4 comes before Tester 3
The technique lies in numeric text substitution for b.name if there's an order_override(non-null):
Above code is Postgres, to convert to Sql Server, remove the word
RECURSIVE, changeREPEATtoREPLICATE,||to+.Equivalent of...
...is:
这是我想出的最终解决方案。它创建一个由点分隔成子部分的字符串。下面的解决方案仅支持根节点中最多 9999 个项目，但您可以通过简单地更改
STR(ItemOrder,4)命令中的数字来增加前导零的数量，从而轻松扩展此范围。Here is the final solution that I came up with. It creates a string which is separated into sub sections by dots. The solution below will only support up to 9999 items in the root node but you could easily extend this by increasing the number of leading zeros by simply changing the number in the
STR(ItemOrder,4)command.兄弟姐妹的数量是已知值吗？已知级别数吗？
如果是这样，您可以对 ItemOrder 执行操作，以保证每个项目都有唯一的 ItemOrder，然后仅按该值排序。
例如，假设任何项目的子项不能超过 10 个（ItemOrder 范围从 0 到 9），并且最多有 5 个级别。我现在要做的是，使第一个父 ItemOrder 为当前 ItemOrder 的 10000 倍，其子 ItemOrder 为当前 ItemOrder 的 1000 倍加上其父 ItemOrder，依此类推，每次删除 0你下降了一个级别。
如果级别或子项的数量未知，您可以采用类似的方法，但您可以构建一个字符串 ItemOrder，而不是构建数字 ItemOrder，从而保证字符串“1.10.20”低于字符串“2.1”
Is the number of siblings a known value? Is the number of levels known?
If so, you can perform operations over the ItemOrder, to guarantee that every item has a unique ItemOrder, and then just sort by that value.
For example, suppose that any item can't have more than 10 childs (ItemOrder ranges from 0 to 9) and there are at most 5 levels. What I'm going to do now, is to make the first parent ItemOrder to be 10000 time it's current item order, ant it's childer ItemOrder would be 1000 times it's current ItemOrder plus it's parent ItemOrder, and so on, removing a 0 each time you go a level down.
If the number of levels or children is unknown, you can go with a similar approach but instead of building a numeric ItemOrder you can build a string ItemOrder, guaranteeing that the string '1.10.20' is lower than the string '2.1'
编辑：答案已更新，最初是按级别排序，没有被要求。
另外，答案没有经过测试。再次更新，种子查询未对 IS NULL
EDIT2 进行过滤：
这是一个更新，将使用浮点数和子查询来获取叶子/分支的最大数量；假设 ItemOrder 是升序的，从 1 开始，没有空洞，并且为每个父项重新启动。
这可以转换回使用整数，因为这样排序如何随着级别数溢出/失去精度将更加明显。
EDIT: Answer updated, originally was sorting by Level, which was not asked of.
Also, the answer is not tested. Updated again, the seed query was not filtering on IS NULL
EDIT2:
Here's an update that will use floats and subquery to get the maximum number of leafs/branches; an assumption is made that the ItemOrder is ascending, starting with 1, with no holes and that it is restarted for each parent.
This could be converted back to using integers as then it will be more obvious how the sorting can overflow/loose precision with number of levels.
尽管这是一篇旧帖子，但我还没有看到给出的答案，而且它似乎没有其他一些答案所具有的缺点。我建议使用 RANK() 函数来正确排序递归结果集。这种方法对于更广泛的数据更宽容一些。此解决方案假设您的递归中任何一个结果下的子结果都不超过 99 个，但如果您有数千、数百万甚至更多，则可以轻松扩展。修改它以适应您的数据集。
在 Ben 的例子中，他会尝试对 ItemOrder 列进行 RANK()。他的解决方案应该是这样的：
Even though this is an old post, I haven't seen this answer given yet, and it doesn't seem to have the drawbacks that some of the other answers have. I recommend using the RANK() function to properly order your recursive result set. This method is a bit more forgiving with wilder data. This solution assumes that you will have no more than 99 sub-subresults beneath any one result in your recursion, but it can easily be expanded if you have thousands, millions, or even more. Modify it to work with your data set.
In Ben's case, he would try to RANK() the ItemOrder column. His solution should look something like this:
SQL 不支持“层次结构”或“树”或“图形”类型，因为 SQL/关系模型本质上是为了使这些类型过时（需要）而发明的。
您编写了一个查询，用于计算数学术语中称为“传递闭包”的内容。我怀疑这真的是你想要的。如果关系（“表”）具有对 (1 2) 和 (2 3)，则您的查询将包括结果对 (1 3)。然而，（在这个例子中）我怀疑你不希望你的 XML 风格的结果包含一个将数字 3 作为数字 1 的直接子代的标签......
我怀疑你想要的更有可能通过以下方式实现使用关系代数的 GROUP 运算符。警告：这实际上与“GROUP BY”不同（关系代数的 GROUP 运算符生成的表包含其值本身就是表的列 - 例如，包含某个父级的所有直接子级的表），并且它是您的特定 DBMS 很可能不支持它，在这种情况下，您几乎“被您的 DBMS 抛弃”并且“除了编写所有该死的狗屎代码之外别无选择（我指的是递归）你自己”。
SQL does not support a 'hierarchy' or 'tree' or 'graph' type, because SQL/the relational model were essentially invented with the purpose of rendering (the need for) those types obsolete.
You have written a query that computes what is known in mathematical terms as a "transitive closure". I doubt that this is really what you want. If a relation ("table") has the pairs (1 2) and (2 3), then your query would include the resulting pair (1 3). However, (in this example) I suspect that you wouldn't want your XML-style result to include a tag holding the number 3 as a direct child of number 1 ...
I suspect what you want is more likely to be achieved by using the GROUP operator of the relational algebra. Caveat : this is not really the same thing as "GROUP BY" (the GROUP operator of the relational algebra produces tables that contains columns whose value is itself a table - e.g. a table holding all the direct children of some parent), and it is quite likely that your particular DBMS doesn't support it, in which case you're left pretty much "abandoned by your DBMS" and "with no other option than to code all the freaking shit (by this I mean the recursion in particular) yourself".