Problems with SQL query tree nodes

It is based on the child node to find the grandparent node under the root node, usually I will use the function.

create function [dbo].[getunderrootparent]

id int)

returns int

declare @aid

select @aid=parentid from the table where id=@id

if @aid is null

return null

elseif @aid=1

return @id

else return

You can call the function in SQL to get the target ID number.

Use standard SQL WITH to implement recursive query (sql2005 or above is definitely supported, sql2000 is not clear whether it is supported):

with subqry(id,name,pid) as (select id,name,pid from test1 where id = 5

union all

select ,,from test1,subqrywhere =

select * from subqry;

What does the structure of your table look like, does it contain two fields, child node and parent node?

The select statement in Oracle can be used to start with....The Connect by Prior clause implements recursive queries, and the basic syntax of Connect By is used in structured queries:

select * from tablename start with cond1

connect by prior cond2

where cond3;

To put it simply, a tree structure is stored in a table, for example, there are two fields in a table

id,parentid。Then by indicating who the parent of each record is, a tree structure can be formed.

A query with the above syntax can fetch all records of the tree.

cond1 is the qualifier of the root node, and of course the qualifier can be relaxed to obtain multiple root nodes, which is actually multiple trees.

cond2 is the connection condition, where the previous record is represented by prior, for example, connect by prior id=praentid means that the id of the previous record is the praentid of this record, that is, the father of this record is the previous record.

cond3 is a filter condition that is used to filter all records returned.

In oracle, there is a type of query called a hierarchical query that does what you call it.

select * from table start with id=start node connectionby prior id=parentid;

You can try it, and you can learn the specific grammar online.

Is it a direct word node or a word node that includes child nodes..?