开发者

SQL Table with parent and child nodes (navigation table)

I have a single table with both parent and child nodes, and each has an order number in it.

I am trying to write a single query to output them in order, its for a navigation list with categories and sub-categories.

I could manage it in code rather than in the SQL query but it would involve calling a query from within a query loop - which I want to avoid.

Table:

DBID | Title | ParentID | OrderNum
 1      aaa       0          1
 2      bbb       0          2
 3      ccc       1开发者_运维百科          1
 4      ddd       1          2
 5      eee       2          1

and I want to get a result set like:

DBID | Title | ParentID | OrderNum
 1      aaa       0          1      <<< main 
 3      ccc       1          1      <<< child
 4      ddd       1          2      <<< 2nd child
 2      bbb       0          2      <<< main 
 5      eee       2          1      <<< child

I have been looking at using a recursive SQL select or Common Table Expressions (CTE) but have not been able to figure it out yet.

Can anyone help point me in the right direction?

(Using SQL Server 2005 / ASP.Net C#)

Edit: I should clarify that I only need it to go to 2 levels - i.e. parent and child, so it does not need to re-curse indefinitely, so I assume the answer is much simpler than I have been looking at.


Is recursion necessary? This appears to give the correct result:

declare @t table 
(DBID           int
,Title          varchar(10)
,ParentID       int
,OrderNum       int
)

insert @t
      select 1,'aaa',0,1
union select 2,'bbb',0,2
union select 3,'ccc',1,1
union select 4,'ddd',1,2
union select 5,'eee',2,1

select * 
from @t
order by ISNULL(NULLIF(ParentID,0),DBID)
        ,ParentID
        ,OrderNum
        ,DBID

EDIT

An explanation:

Since the input rows correspond 1:1 with the output rows and it's only the row order which changes, this must be an ordering problem. The solution is to identify the parent-child group to which each row belongs, and sort by that first, then all the other values.

The only difficult part of doing this is that parent rows (with ParentID = 0) gets its group membership implicitly from its DBID, whilst all the other rows get it from ParentID.

The first WHERE condition (ISNULL(NULLIF(ParentID,0),DBID)) handles this. It could also be written as

CASE WHEN ParentID = 0
     THEN DBID
     ELSE ParentID
END

I used NULLIF...ISNULL since it's more succinct, but if the sorting conditions were more complex I would have used CASE...WHEN.


A couple of years ago, I found the easiest way to do this (after hunting around) was to add "depth" and "path" columns to the table:

DBID | Title | ParentID | OrderNum | Path  | Depth
 0      Root     null        1       /         0
 1      aaa       0          1       /1/       1
 2      bbb       0          2       /2/       1
 3      ccc       1          1       /1/3/     2
 4      ddd       1          2       /1/4/     2
 5      eee       2          1       /2/5/     2

I then have a pair of triggers on the table to automatically update these on insert and update:

Insert Trigger

ALTER TRIGGER [dbo].[SiteMap_InsertTrigger] 
  ON  [dbo].[SiteMap] 
  AFTER INSERT
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  UPDATE child
    -- set the depth of this "child" to be the
    -- depth of the parent, plus one.
    SET Depth = ISNULL(parent.depth + 1, 0),
      -- the lineage is simply the lineage of the parent,
      -- plus the child's ID (and appropriate '/' characters
      Path = ISNULL(parent.Path, '/') + LTrim(child.DBID) + '/'

    -- we can't update the "inserted" table directly,
    -- so we find the corresponding child in the
    -- "real" table
    FROM SiteMap child INNER JOIN inserted i ON i.Id = child.DBID
      -- now, we attempt to find the parent of this
      -- "child" - but it might not exist, so these
      -- values may well be NULL
      LEFT OUTER JOIN SiteMap parent ON child.ParentId = parent.DBID
END

Update Trigger

ALTER TRIGGER [dbo].[SiteMap_UpdateTrigger] 
  ON  [dbo].[SiteMap] 
  AFTER UPDATE
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- if we've modified the parentId, then we
  -- need to do some calculations
  IF UPDATE (ParentId)
  BEGIN
    UPDATE child
      /*
      to calculate the correct depth of a node, remember that
        - old.depth is the depth of its old parent
        - child.depth is the original depth of the node
          we're looking at before a parent node moved.
          note that this is not necessarily old.depth + 1,
          as we are looking at all depths below the modified
          node
      The depth of the node relative to the old parent is
      (child.depth - old.depth), then we simply add this to the
      depth of the new parent, plus one.
      */
      SET Depth = child.Depth - old.Depth + ISNULL(parent.Depth + 1,0),
        Path = ISNULL(parent.Path,'/') + LTrim(old.DBID) + '/' +
        right(child.Path, len(child.Path) - len(old.Path))
        -- if the parentId has been changed for some row
        -- in the "inserted" table, we need to update the
        -- fields in all children of that node, and the
        -- node itself                 
      FROM SiteMap child
      INNER JOIN inserted old ON child.Path LIKE old.Path + '%'
        -- as with the insert trigger, attempt to find the parent
        -- of the updated row
      LEFT OUTER JOIN SiteMap parent ON old.ParentId=parent.DBID
  END;
END;

Which then makes it a lot easier to jump into a particular category and pull out all the children and sub-children very quickly by searching across Path or depth.

Interestingly, I've just found that SQL Server 2008 includes a new type: HierarchyId which basically rolls the Path column up into a compact type.


Yes, you can achieve this by using either recursive sql or cte.

I think this solution will help you to solve this hierarchical table select.

http://geekswithblogs.net/dotNETPlayground/archive/2007/12/28/118017.aspx


You could use the nested-set model. This allows whole trees to be fetched in one SQL statement. The disadvantage is that write operations become more complex.

Alternatively you could write a recursive stored procedure.


This a recursive approach. using CTE.

with cte
as
(
select *, 1 as [level], [DBID] as [father]
from @t
where parentID=0
union all
select t.*, [level]+1, cte.[father]
from @t t
inner join cte on cte.DBID=t.parentID
)
select  [DBID],Title, ParentID,  OrderNum  
from cte
order by [father], [level],  OrderNum  


The accepted answer is spot on. Here it is in linq as well:

var orderedItems = from item in dataContext.items
                   orderby (item.ParentID == 0 ? item.DBID : item.ParentID),
                       item.ParentID , item.OrderNum, item.DBID
                   select item;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜