AI教我一條SQL實現明細轉樹形結構

1. 原始數據

2. 目標數據

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;

4. 總結

實現過程使用AI輔助生成測試代碼,通過一次交互提示詞,返回目標代碼.關注微信公眾號[小滿小慢],回復關鍵詞AIGROUP可獲取具體提示內容.

原文地址: https://mp.weixin.qq.com/s/9_A6cgohb8sX1N0RME0usQ