分层数据库选择/插入语句（SQL服务器） [英] Hierarchical Database Select / Insert Statement (SQL Server)

问题描述

我最近在一个偶然发现的问题与从1表中选择关系的细节和插入到另一个表，我希望有人可以提供帮助。

I have recently stumbled upon a problem with selecting relationship details from a 1 table and inserting into another table, i hope someone can help.

我有一个表的结​​构如下：

I have a table structure as follows:

ID (PK)   Name       ParentID<br>
1         Myname     0<br>
2         nametwo    1<br>
3         namethree  2

例如

这是我需要从中选择并得到所有的关系数据的表。作为有可能是子链接的数量不受限制（有一个功能，我可以为该打造循环？）

This is the table i need to select from and get all the relationship data. As there could be unlimited number of sub links (is there a function i can create for this to create the loop ?)

然后，一旦我有我需要插入到另一个表中的数据和ID的，现在必须改变作为ID的必须按顺序去（例如：我不能有ID为2是3例如子）我希望我可以使用相同的功能，选择做插入。

Then once i have all the data i need to insert into another table and the ID's will now have to change as the id's must go in order (e.g. i cannot have id "2" be a sub of 3 for example), i am hoping i can use the same function for selecting to do the inserting.

推荐答案

如果您使用的是SQL Server 2005或以上，你可以使用递归查询来获取您的信息。下面是一个例子：

If you are using SQL Server 2005 or above, you may use recursive queries to get your information. Here is an example:

With tree (id, Name, ParentID, [level])
As (
    Select id, Name, ParentID, 1
    From [myTable]
    Where ParentID = 0

    Union All

    Select child.id
    	  ,child.Name
    	  ,child.ParentID
    	  ,parent.[level] + 1 As [level]
    From [myTable] As [child]
    Inner Join [tree] As [parent]
    On [child].ParentID = [parent].id)
Select * From [tree];

此查询将返回的第一部分（其中PARENTID = 0），所有的子行的递归请求的行。这是否帮助你？

This query will return the row requested by the first portion (Where ParentID = 0) and all sub-rows recursively. Does this help you?

我不知道我知道你想和你的插入发生什么。你可以预期的结果方面提供更多的信息，当你做了什么？

I'm not sure I understand what you want to have happen with your insert. Can you provide more information in terms of the expected result when you are done?

祝你好运！

这篇关于分层数据库选择/插入语句（SQL服务器）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！