可能吗?? [英] Is It Possible??

问题描述

我有一个表组
contains

GroupID   GroupName ChildId
1           G1        0(0-Means Root Group)
2           G2        0
3           G3        0
4           G4        0
5           G5        1(The GroupId of First Row)
6           G6        5(The GroupId of Fifth Row)
7           G7        2(The GroupId of 2nd Row)
8           G8        7(The GroupId of 7th Row)
9           G9        8(The GroupId of 8th Row)

Now I Want The Group Name Of Root Group
Through Sql query only I want to get
the Name Of the main Group Through its child Id.
For Ex. If I select 9(Group Id) It Is Linked With Main Id-2
9->8->7-2-0(Means Group Name-G2)-Where we will get zero it is over

Thank You
Sukhen Dass

解决方案

如果您有机会使用Oracle，答案将是:

  SELECT  GroupID
 FROM 组
位置 ChildID =  0 
START 与 GroupID =  9 
CONNECT  BY  PRIOR ChildID = GroupID 

如果您拥有sqlserver 2008，则可以使用子查询分解

 使用 t  AS (
    选择 GroupID，ChildId
     FROM 组g
    位置 GroupID =  9 
        联盟(Union) 所有
    选择 o.GroupID，o.ChildId
     FROM 组g  join  t
        打开 g.GroupID = t.ChildId
    )
选择 t.GroupID
 FROM  t
位置 ChildId =  0  

您有3个选项.

您正在使用的是递归数据结构.它将要求您使用递归或迭代来找到根节点.您将需要一个 while循环，该循环在前一个节点上重复执行SELECT语句以查找其子级"(在递归术语中，通常称为父级"，作为子级)可以有一个父母，但一个父母可能有很多孩子.

一种替代方法是使用"公用表表达式"创建递归查询，尽管此功能仅在SQL Server 2008中可用.您可以阅读有关该技术的更多信息 解决方案

If you by any chance would be using Oracle the answer would be:

SELECT  GroupID
FROM    Groups
WHERE   ChildID = 0
START   WITH GroupID = 9
CONNECT BY PRIOR ChildID = GroupID

If you have sqlserver 2008 you can use subquery factoring

With  t AS (
    SELECT  GroupID,ChildId
    FROM    Groups g
    WHERE   GroupID = 9
        UNION ALL
    SELECT  o.GroupID,o.ChildId
    FROM    Groups g join t
        ON  g.GroupID = t.ChildId
    )
SELECT  t.GroupID
FROM    t
WHERE   ChildId = 0

You have 3 options.

What you are using is a recursive data structure. It will require that you use recursion or iteration to find the root node. You will need a while loop that repeatedly performs a SELECT statement on the previous node to find it''s "child" (in recursive terminology, this would usually be called the "parent", as a child can have one parent but one parent may have many children).

An alternative would be to use a "common table expression" to create a recursive query, though this feature is only available in SQL Server 2008. You can read more about that technique here.

Finally, you can also create a recursive function that basically does what the while loop does, but in a recursive fashion. Note, however, that some versions of SQL Server have limitations on the allowable depth of recursion, though I''m not sure if that is restricted to just stored procedures (you can create a function instead of a stored procedure).

Nope (I guess): you cannot do that with a SQL query.
I suppose you have to iterate (or possibly recurse).
BTW I''m not a database expert, but you table hardly follows any normalization rule...
:)

这篇关于可能吗??的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！