SQL Practical application-recursive query, department recursive sort

1, since to talk about SQL, database tables are necessary

2. Data structure

3. Get all child nodes of a node

The traditional writing (sql2000) is very troublesome, and for the moment it is not written.

Look at the formulation of the CTE.

CREATE PROCSp_gettreebyid (@TreeId int) asBEGIN withCtetree as(SELECT * fromTuzitreeWHEREId= @TreeId  --first query as a base point for recursion (anchor point)UNION  AllSELECTTuzitree.*     --This recursion ends when the second query is a recursive member and the result of the subordinate member is empty.  fromCtetreeINNER JOINTuzitree onCtetree.id=Tuzitree.parentid)SELECT * fromCtetreeEND

Test it.

exec  Sp_gettreebyid  @TreeId=1001

Results

-----------------------------------------------

4, use the node path to do (each node path to save its own path and all the parent node's path = itself and all parent Node Association)

5, since there is a path

So querying all of its child nodes requires only where Nodepath like '/1001/% '.

This will be much simpler, plus an index.

Summarize:

If we need to load on demand, we can load all of its child nodes when we click on the node.

If the change is not small, you can use the cache . Such processing can meet many business needs.

Good table design will bring more convenience to later development and change of demand.

The next time you continue to summarize the knowledge of SQL, the case has evolved from the actual work.

From for notes (Wiz)

SQL Practical application-recursive query, department recursive sort