Calculate the Hierarchy of Recursive References — From SQL to SPL #11

Problem description & analysis: A certain table in the MS SQL database has a multi-level self-association structure, where the second field parent node ID is a foreign key pointing to the first field node ID of the table, and the third field is a zone. Task: Now we need to find the hierarchy of nodes with a higher level of 2 or more, as well as the zones of the highest-level nodes. For example, the first record has 3 levels of hierarchy, which are 5–11–15, and the highest level is 15; The second record has two levels of superiors, namely 6–12, with the highest level being 12. Code comparisons: SQL solution： WITH dt AS ( SELECT temp.product_identifier, temp.parent_product_identifier, temp.Zone, 1 AS hierarchy, parent_product_identifier AS current_parent FROM temp UNION ALL SELECT dt.product_identifier, dt.parent_product_identifier, temp.Zone, dt.hierarchy+1, temp.parent_product_identifier AS current_parent FROM dt INNER JOIN temp ON temp.product_identifier = dt.current_parent ) SELECT product_identifier, parent_product_identifier, hierarchy, Zone FROM dt WHERE hierarchy > 1 AND hierarchy = ( SELECT MAX(hierarchy) FROM dt dt2 WHERE dt2.product_identifier = dt.product_identifier) ORDER BY product_identifier; WITH dt AS ( SELECT temp.product_identifier, temp.parent_product_identifier, temp.Zone, 1 AS hierarchy, parent_product_identifier AS current_parent FROM temp UNION ALL SELECT dt.product_identifier, dt.parent_product_identifier, temp.Zone, dt.hierarchy+1, temp.parent_product_identifier AS current_parent FROM dt INNER JOIN temp ON temp.product_identifier = dt.current_parent ) SELECT product_identifier, parent_product_identifier, hierarchy, Zone FROM dt WHERE hierarchy > 1 AND hierarchy = ( SELECT MAX(hierarchy) FROM dt dt2 WHERE dt2.product_identifier = dt.product_identifier) ORDER BY product_identifier; As long as all the levels recursively referenced by each node are found, the results can be easily filtered out. However, SQL does not have directly available functions, and complex recursive subqueries plus self joins need to be used to implement them. The code is lengthy and difficult to understand. SPL solution: SPL provides directly available functions that can obtain all levels of recursive references to nodes.

Mar 19, 2025 - 03:55

Calculate the Hierarchy of Recursive References — From SQL to SPL #11

Problem description & analysis:

A certain table in the MS SQL database has a multi-level self-association structure, where the second field parent node ID is a foreign key pointing to the first field node ID of the table, and the third field is a zone.

Task: Now we need to find the hierarchy of nodes with a higher level of 2 or more, as well as the zones of the highest-level nodes. For example, the first record has 3 levels of hierarchy, which are 5–11–15, and the highest level is 15; The second record has two levels of superiors, namely 6–12, with the highest level being 12.

Code comparisons:

SQL solution：

WITH dt AS (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent 
  FROM temp
  UNION ALL
  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, 
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1 
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) 
ORDER BY product_identifier;
WITH dt AS (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent 
  FROM temp
  UNION ALL
  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, 
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1 
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) 
ORDER BY product_identifier;

As long as all the levels recursively referenced by each node are found, the results can be easily filtered out. However, SQL does not have directly available functions, and complex recursive subqueries plus self joins need to be used to implement them. The code is lengthy and difficult to understand.

SPL solution:

SPL provides directly available functions that can obtain all levels of recursive references to nodes.