SQL Server 中的递归好吗? [英] Is recursion good in SQL Server?
问题描述
我在 SQL Server 中有一个表,它具有 Item_ID、Item_ParentID 的正常树结构.假设我想迭代并获取特定 Item_ID(在任何级别)的所有 CHILDREN.
I have a table in SQL server that has the normal tree structure of Item_ID, Item_ParentID. Suppose I want to iterate and get all CHILDREN of a particular Item_ID (at any level).
递归似乎是这个问题的直观候选,我可以编写一个 SQL Server 函数来执行此操作.
Recursion seems an intuitive candidate for this problem and I can write an SQL Server function to do this.
如果我的表有很多条记录,这会影响性能吗?如何避免递归并简单地查询表?请问有什么建议吗?
Will this affect performance if my table has many many records? How do I avoid recursion and simply query the table? Please any suggestions?
推荐答案
在新的 MS SQL 2005 中,您可以使用 WITH
关键字
With the new MS SQL 2005 you could use the WITH
keyword
对于 Oracle,您可以使用 CONNECT BY
关键字来生成分层查询 (语法).
With Oracle you could use CONNECT BY
keyword to generate hierarchical queries (syntax).
AFAIK with MySQL 你必须使用递归.
AFAIK with MySQL you'll have to use the recursion.
或者,您始终可以为您的记录父->子关系构建一个缓存表
Alternatively you could always build a cache table for your records parent->child relationships
这篇关于SQL Server 中的递归好吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!