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;
精彩评论