从 SQL 到 SPL：分组后每组前面增加符合条件的记录

MSSQL 数据库某表具有多层级的自关联结构，第 2 个字段父节点 ID 是指向本表的第 1 个字段节点 ID 的外键，第 3 个字段是区域。

product_identifier	parent_product_identifier	Zone
1	5	E
2	6	F
3	7	G
4	8	H
5	11	R
6	12	B
7	13	C
8	14	D
11	15	A

现在要找出上级层数大于等于 2 的那些节点的层级，以及最高层节点的区域，比如第 1 条记录的上级有 3 层，分别是 5-11-15，最高层是 15；第 2 条记录的上级有 2 层，分别是 6-12，最高层是 12。

product_identifier	hierarchy	Zone
1	3	A
2	2	B
3	2	C
4	2	D
5	2	A

SQL解法：

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;

只要找到各节点递归引用的所有层级，就可以方便地过滤出结果，但SQL没有直接可以用的函数，要用结构复杂的递归子查询+自关联join来实现，代码冗长难懂。

SPL提供了直接可用的函数，可以获得节点递归引用的所有层级。

	A
1	=mssql.query("select product_identifier,parent_product_identifier,zone from temp order by product_identifier”)
2	=A1.switch(parent_product_identifier, A1:product_identifier)
3	=A2.derive(~.prior(parent_product_identifier):t, t.len():hierarchy, t.m(-1).zone:z)
4	=A3.select(hierarchy>=2)
5	=A4.new(product_identifier, hierarchy, z:zone)

A2：建立引用关系，将parent_product_identifier替换成外键指向的本表的记录。

A3：新增计算列，用prior函数计算出本节点递归引用的所有层级，这些层级的层数以及最后一个层级的区域。

A4：选出递归引用的所有层级的层数大于等于2的节点。

A5：生成目标结果集。

SPL以开源免费，欢迎前往乾学院了解更多！

源码地址

免费下载