One of the data hierarchy models

1, in the real world, there are many phenomena exist hierarchical structure, the company's personnel title is a typical level of results, such as

SQL Server is a relational db, suitable for storing data in two-dimensional relationships, how to store data with hierarchical structure? You need to use a field parentid to represent the ancestor ID, the sample table structure is as follows

Create Table  int notnullprimarykeyint foreign  Keyreferencesvarchar (NOT null )

2. Inserting sample Data

Insert  intodbo.emph (ID,PARENTID,DESCR)Values(1,NULL,'boss'),(2,1,'M1'),(3,1,'M2'),(4,1,'M3'),(5,1,'M4'),(6,2,'L11'),( -,2,'L12'),(7,2,'L13'),(8,2,'L14'),(9,2,'L15'),(Ten,3,'L21'),( One,3,'L22'),( A,3,'L23'),( -,3,'L24'),( the,6,'E111'),( -,6,'E112'),( -,6,'E113'),( -,6,'E114'),( +, -,'E121'),( +, -,'E122'),( A, -,'E123')

3, use CTE recursive query M1 all employees, including leader and employee

; withCTE (ID,PARENTID,DESCR) as(SelectID,PARENTID,DESCR fromdbo.emphwhereId=2Union  AllSelectE.ID,E.PARENTID,E.DESCR fromdbo.emph eInner JoinCTE C onE.parentid=c.id)Select * fromCTEOrder  byParentID

4, look at the level of the query nesting, the sample code is as follows

; withCTE (ID,PARENTID,DESCR, Level) as(SelectID,PARENTID,DESCR,0  as  Level fromdbo.emphwhereId=2Union  AllSelectE.ID,E.PARENTID,E.DESCR, Level+1  as  Level fromdbo.emph eInner JoinCTE C onE.parentid=c.id)Select * fromCTEOrder  byParentID

5, view the path of each row of data, easy to see the attribution relationship, path is defined using the ID

; withCTE (PATH,ID,PARENTID,DESCR, Level) as(Select cast('\'+cast(ID as varchar) as varchar( -)) asPath
ID,PARENTID,DESCR,0  as  Level fromdbo.emphwhereId=2Union  AllSelect cast(C.path+'\'+ cast(e.id as varchar) as varchar( -)) aspath, E.ID,E.PARENTID,E.DESCR, Level+1  as  Level fromdbo.emph eInner JoinCTE C onE.parentid=c.id)Select * fromCTEOrder  byParentID

One of the data hierarchy models