Forem

Judy
Judy

Posted on

2 1 1 1 1

SQL: Recursive calculation of tree like hierarchical structure #eg83

SQL SERVER has a task table where the id field is the primary key and the parent field is the foreign key pointing to the id, used to describe how multiple nodes form a tree. The root node of a tree is null, and the records with a null parent are the child nodes of the root, that is, the secondary nodes. The weight field only has a value when it is a leaf node.

Image description

Now we need to recursively calculate the hierarchical structure of the tree. Parallel nodes are arranged in order of their IDs, and the new name field=level * underline + original name, which is used to visually represent the hierarchical relationship; The new weight field is the sum of the weights of all subordinate nodes, and the level field represents the hierarchy.

Image description
SPL code:

Image description
A1: Query the database through JDBC.

A2: Create an empty result set.

A3: Filter out the child nodes of the root and loop through these nodes using recursive function.

A4-B6: Function tree, which adds new records to the result set, recursively processes directly subordinate nodes, and returns the weight of the new record. It has four parameters: id, name, weight, and level.

B4: Add new records to the result set and spell out the new name and level. The new weight will be calculated later, so let's set it to 0 for now.

B5: Filter out the direct subordinate nodes of this node, recursively calculate the weights of these nodes, and sum them up.

B6: Calculate the new weight and modify this record to return the new weight.

A7: Return the result set.

Free download

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay