CODEWITHSUNDEEP
sqlt-sqlbinary-tree
Squall Leonhart
Squall Leonhart

Reputation: 439

T-SQL Count child node in Binary Tree?

I made a table to store a Binary Tree like below:

- NodeID
- NodeLeft
- NodeRight

NodeLeft store the ID of the left node. And Node right store the ID of the right node.

I need to write a Procedure that if i pass a NodeID, it'll count how many child node on the left and how many child node on the right. Can separate to 2 Procedure.

Upvotes: 2

Views: 1173

Answers (2)

Squall Leonhart
Squall Leonhart

Reputation: 439

I found this topic. http://www.sqlservercentral.com/Forums/Topic1152543-392-1.aspx

The table structure is different from my designed table. But it is an Binary Tree so i can use it.

And the SQL Fiddle is very helpful.

Upvotes: 0

András Ottó
András Ottó

Reputation: 7695

Try this:

WITH CTE_Node(
  NodeID,
  NodeRigth,
  NodeLeft,
  Level,
  RigthOrLeft
  )
AS
(
SELECT 
 NodeID,
  NodeRigth,
  NodeLeft,
  0 AS Level,
  'P'
  FROM Node
  WHERE NodeID = 1

  UNION ALL

  SELECT 
 Node.NodeID,
  Node.NodeRigth,
  Node.NodeLeft,
  Level + 1,
  CASE WHEN CTE_Node.NodeLeft = Node.NodeID THEN 'R' ELSE 'L' END
  FROM Node
INNER JOIN CTE_Node ON CTE_Node.NodeLeft = Node.NodeID
  OR CTE_Node.NodeRigth = Node.NodeID
  )
SELECT DISTINCT RigthOrLeft, 
COUNT(NodeID) OVER(PARTITION BY RigthOrLeft)  
FROM CTE_Node

Here is an SQL Fiddle. The Level is just there to see how is it working. May you can use it later.

Upvotes: 4

Related Questions