AI教我一条SQL实现明细转树形结构-优快云博客

AI教我一条SQL实现明细转树形结构

1. 原始数据

序号	COUNTRY	PROVINCE	CITY	GAME
1	中国	广东	深圳	地心侠士
2	中国	广东	广州	地心侠士
3	中国	江苏	南京	地心侠士
4	美国	加州	洛杉矶	地心侠士

2. 目标数据

序号	节点名称	节点ID	父级节点ID	国家	省	城市	微信小游戏
1	美国	美国	0	美国
2	加州	美国加州	美国	美国	加州
3	洛杉矶	美国加州洛杉矶	美国加州	美国	加州	洛杉矶	地心侠士
4	中国	中国	0	中国
5	广东	中国广东	中国	中国	广东
6	广州	中国广东广州	中国广东	中国	广东	广州	地心侠士
7	深圳	中国广东深圳	中国广东	中国	广东	深圳	地心侠士
8	江苏	中国江苏	中国	中国	江苏
9	南京	中国江苏南京	中国江苏	中国	江苏	南京	地心侠士

3. 实现思路

从一个明细数据,提取多余的父级信息,可以使用不同维度的分组进行提取.可以逐层获取,也可以使用GROUPING SETS
减少分组代码量.比如以上提供的目标数据中可以如下汇总实现:

汇总国家层级,按照country分组汇总,得到中国,美国,两条数据
汇总省份层级,按照country,province,得到中国广东,中国江苏,美国加州三条数据
汇总明细数据,直接按照所有列进行分组,得到明细数据

以上步骤,可以解决数据问题,但是数据之间的层级关系体现不出来,需要添加三列,分别表示节点名称,节点ID,父级节点ID
可以说使用分组函数GROUPING_ID实现,分组列为二进制,包含分组为0不包含为1,国家,省,明细三个分组值依次为

汇总国家层级 GROUPING_ID(country, province, city),二进制 011 十进制 3,节点名称 country 节点ID country 父级节点ID ‘0’
汇总省份层级 GROUPING_ID(country, province, city),二进制 001 十进制 1,节点名称 province 节点ID country||province 父级节点ID country
汇总明细层级 GROUPING_ID(country, province, city),二进制 000 十进制 0,节点名称 city 节点ID country||province||city 父级节点ID country||province

分组函数详细参考可以查看 sql分组 group by rollup,cube,grouping sets,group_id,groupingId

WITH region_data AS (
  SELECT '中国' as country, '广东' as province, '深圳' as city ,'地心侠士' as game FROM dual UNION ALL 
  SELECT '中国', '广东', '广州' ,'地心侠士' FROM dual UNION ALL
  SELECT '中国', '江苏', '南京','地心侠士' FROM dual UNION ALL
  SELECT '美国', '加州', '洛杉矶','地心侠士' FROM dual
)
SELECT 
    CASE 
        WHEN GROUPING_ID(country, province, city) = 0 THEN city
        WHEN GROUPING_ID(country, province, city) = 1 THEN province
        WHEN GROUPING_ID(country, province, city) = 3 THEN country
    END AS 节点名称,
   CASE 
        WHEN GROUPING_ID(country, province, city) = 0 THEN country||province||city
        WHEN GROUPING_ID(country, province, city) = 1 THEN country||province
        WHEN GROUPING_ID(country, province, city) = 3 THEN country
    END AS 节点ID,
     CASE 
        WHEN GROUPING_ID(country, province, city) = 0 THEN country||province
        WHEN GROUPING_ID(country, province, city) = 1 THEN country
        WHEN GROUPING_ID(country, province, city) = 3 THEN '0'
    END AS 父级节点ID,
    country AS 国家,
    province AS 省,
    city AS 城市,
    game as 微信小游戏
FROM region_data
GROUP BY GROUPING SETS (
    (country, province, city,game),
    (country, province),     
    (country)               
)
ORDER BY country, province nulls first, city nulls first;

前端构造树形结构数据,传递给前端有时有特殊字符,方便处理,可以统一转换成16进制串,针对节点ID,父级节点ID,

  function NODEID_TO_RAWID(str in varchar2) return varchar2 is
    new_str varchar2(1000);
    -- 微信公众: 小满小慢 20251113
  begin
    new_str:=UTL_RAW.CAST_TO_RAW(str);
    return new_str;
  end;