All geek questions in one place

SQL table with parent and child nodes (navigation table)

I have one table with parent and child nodes, each of which has an order number.

I am trying to write one query to display them in order, its for a navigation list with categories and subcategories.

I could manage it in the code, and not in the SQL query, but it would include calling the query from the 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, for example:

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 was looking for using recursive SQL or Common Table Expressions (CTE) choices, but haven't been able to figure it out yet.

Can anyone point me in the right direction?

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

: , 2 - , , , , .

+1
6

? , :

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

:

1:1 , , , . , - , , , .

, ( ParentID= 0) DBID, ParentID.

WHERE (ISNULL(NULLIF(ParentID,0),DBID)) .

CASE WHEN ParentID = 0
     THEN DBID
     ELSE ParentID
END

NULLIF...ISNULL, , , CASE...WHEN.

+2

( ) "" "" :

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

, :

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 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


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;

.

, , SQL Server 2008 : HierarchyId, Path .

+1

. SQL. , .

.

0
source

This is 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 @tt
inner join cte on cte.DBID = t.parentID
)
select [DBID], Title, ParentID, OrderNum  
from cte
order by [father], [level], OrderNum
0
source

The accepted answer is spot. Here it is also in linq:

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

Source: https://habr.com/ru/post/1730600/

More articles:


All Articles