EDIT:

Question

附录一与父母与子女的关系。

parent  child
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

现在我有这样一个问题,即回到一个人口名单(例如1和2),我想找到他们的所有子女、孙辈等。 (在本案中:4、5、6、8、9、11)。

我知道,我可以采用共同的表格来重新搜索,但我很想知道,我是否可以编写一份文件,以便一劳永逸地找到所有反对者,而不必对所提出的意见加以反驳。

<>Edit:sorry for not being clear sufficient. 我期望的是:

SELECT {Hierarchical relation} from table where parent in (1,2)

应形成一个单一的产出栏,并列载4、5、6、8、9、11。

我不再对产出关系感兴趣,只是对多个家庭的一整套家庭成员感兴趣。

Answer 1

......

---- PlainTable ----
parent  idElement (child)
Null    1
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

WITH tableR (parent, idElement)
AS
(
-- Anchor member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e   
    WHERE parent in (1,2)
    UNION ALL
-- Recursive member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e
    INNER JOIN tableR AS d
        ON e.parent = d.idElement
)
-- Statement that executes the CTE
SELECT idElement
FROM tableR  --inner join to plain table by id if needed

Answer 2

Ok, danihp s solution did put me on the right track. This is the solution I came up with:

DECLARE @Input TABLE (
  id int
)

INSERT INTO @Input VALUES (1),(2)

;WITH Relations (parent, child)
AS
(
    SELECT e.parent, e.child
      FROM RelationTable AS e   
      WHERE parent in (SELECT * FROM @Input)
    UNION ALL
      SELECT e.parent, e.child
        FROM RelationTable AS e
        INNER JOIN Relations AS d
          ON e.parent = d.child
)
SELECT child
FROM Relations

It results in a list of child ids (excluding the 2 parent ids like I said earlier in the question): 4,5,6,8,9,11

Answer 3

http://europa-eu-un.org

采用共同表格表达方式的回收查询

以及

http://www.sqlserver central.com/articles/T-SQL/65540/

可以帮助你们。

Answer 4

2008年5月24日,日内瓦

I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.

我不敢肯定你所说的话。大部分(可能全部) 可以通过使用分水库来完成电离层,但使用分水位的频率会更快。当你说不希望重复投入时,正如你再次谈论使用治疗器一样,你当然可以把它作为一套既定的行动(使用CTEs或子queries)来做,但是,重新入侵没有办法。

Edit: I m sorry, I mm 不要直截了当地......当然,你可以用正常的子宫进行再造,但即使你能够更快地这样做,这一点仍然是的。如果你看不见进行再入侵的战略,那么就试图寻找像2000年再次入侵这样的东西,因为CTE在回头 around。下面是一些例子::http://www.mssqltips.com/sqlservertip/938/recursive-queries-with-sql-server-2000/。当然,对你的回答仍然是相同的。

Answer 5

典型的情况是,我为此使用了。是的,你可以做你的所有工作,事实上,你的产出是可直接附在Kum.net树谱之上的XML。希望这一帮助

链接: 是否有一种简单的方式来调查孩子?

Answer 6

在等待更新职位时:

SELECT DISTINCT 
  p.parent AS parent
, c.child AS child
, IFNULL(g.child,  NONE ) AS grandchild_of_parent
FROM parent_child as p
LEFT JOIN parent_child AS c ON p.parent = c.parent
LEFT JOIN parent_child AS g ON c.child = g.parent;

Results would look like this:

parent  child   grandchild_of_parent
1       4       8
1       5       NONE
2       6       9
3       7       10
4       8       11
6       9       NONE
7       10      NONE
8       11      NONE

Such a simple-minded-but-probably-harder-to-maintain type of code, but since I m not familiar with SQL Server 2008 s built in features to handle this type of request, I ll just throw a long shot...

EDIT:

仅此,在你研究<条码>时,你可以看到海因果结果:和(或)<条码>。 ......这将取得你们的成果,但只有1岁和2岁的孙辈才能这样做。

-- A. Parents 1 and 2
SELECT DISTINCT p.parent FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- B : Children of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- C : Children of B, Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
  SELECT DISTINCT p.child FROM parent_child AS p
  WHERE p.parent IN (1,2)
)
UNION
-- D : Children of C, Great-Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
  SELECT DISTINCT p.child FROM parent_child AS p
  WHERE p.parent IN (
    SELECT DISTINCT p.child FROM parent_child AS p
    WHERE p.parent IN (1,2)
  )
)

我再次强烈建议你研究其他人所投的内容,并探讨他们提供的联系。如果你有重生子女,那么我就不去做最后的长期研究;如果你有重生子女,那将绝对有。

Answer 7

---- PlainTable ----   
parent  idElement (child_id)
    Null    1
    1       4
    1       5
    2       6
    3       7
    4       8
    6       9
    7       10
    8       11

**Table value function to get Child ids at 4(any) Level of parent in the same table:-**

    FUNCTION fc_get_Child_IDs(Parent)
    DECLARE @tbl TABLE (ID int)
    DECLARE @level int=4
    DECLARE @i int=1
    insert into @tbl  values (Parent)
    while @i < @level
    BEGIN

    INSERT into @tbl 
    select child_id from PlainTable where Parent  in  (select ID from @tbl) and child_id not in (select ID from @tbl) 
     set @i = @i + 1
    END

EDIT:

友情链接