Recursive Queries and Hierarchical Retrieval in SQL using mysql 9.0

 

CREATE DATABASE IF NOT EXISTS JewelryAnalytics
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
 
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0;
SET SESSION innodb_lock_wait_timeout = 50;
SET SESSION time_zone = '+00:00';
SET SESSION max_execution_time = 0;
 
-- =============================================================================
--  MySQL 9.0 递归查询与层级检索（Recursive CTE）— 国际化珠宝行业演示
--  主题：Recursive Queries and Hierarchical Retrieval in SQL
--  覆盖：OA / ERP / CRM / HRM / MES / SCM / WMS / KMS 组织图、品类树、BOM、库位树
--  约束：万亿级高并发 / 国际时区 / 国际多币种结算
-- =============================================================================
-- §0.0 运行必读（务必先读，否则会踩本主题特有的坑）
-- -----------------------------------------------------------------------------
--  1) 本脚本在数据库 JewelryAnalytics 内创建一批 rec_* 表与过程，并复用既有
--     agg_dim_country（含 offset_min 时区偏移）、agg_dim_currency（含 usd_rate）
--     维度表与 agg_gl_trade_order（100 万行事实，演示多币种/时区时 JOIN 用）。
--  2) 幂等：所有表/过程均带 IF EXISTS 守卫，可重复执行。首次会造层级样本数据，
--     之后再次运行只重建派生表，不会重复插入。
--  3) ※ 本主题关键事实 1：MySQL 递归 CTE 必须写 WITH RECURSIVE，否则自引用被当作
--     普通表报 1146；深度受 cte_max_recursion_depth 限制（默认仅 1000），深树必超。
--  4) ※ 本主题关键事实 2：MySQL 没有 SQL 标准的 CYCLE 子句（写 CYCLE 报 1064），
--     循环防护必须手动用路径列 + FIND_IN_SET 实现，否则有环数据会无限递归被打断。
--  5) ※ 本主题关键事实 3：递归 CTE 是“逐行迭代、单线程”，不能并行，对万亿边图
--     在线递归不现实；万亿级必须用“闭包表(closure)/路径枚举”物化后再查，或图引擎。
--  6) max_execution_time=0 仅解除服务端单语句 30s 上限；若你在 MySQL Workbench 跑
--     造数时被 30s 客户端读超时掐断（Error 2013），请把 Workbench 的
--     “DBMS connection read timeout interval” 设为 0/600，或改用命令行客户端。
-- =============================================================================
 
-- §0.1 MySQL 9.0 vs SQL 标准：递归能力差异（本机 9.0.1 社区版实测）
-- -----------------------------------------------------------------------------
--  特性                          SQL 标准 / 其他库        MySQL 9.0
--  WITH RECURSIVE                支持                     ✅ 必须显式写 RECURSIVE
--  自引用不写 RECURSIVE          报错                     ❌ 报 1146（表不存在）
--  CYCLE 子句（防环）            标准/DB2/Oracle 12c+     ❌ 报 1064（无此语法）
--  递归成员聚合/GROUP BY/DISTINCT ⨉ 均禁止                 ❌ 报 1140/1064
--  cte_max_recursion_depth       无                       ⚠ 默认 1000，超深树需调大
--  闭包表物化                   应用层                     ✅ 自己用递归 CTE 一次性建
-- =============================================================================
 
-- §0.2 反模式速查表（本主题高频错误）
-- -----------------------------------------------------------------------------
--  ❌ 忘写 RECURSIVE            → ERROR 1146（递归成员引用的 CTE 被当普通表）
--  ❌ 深度超 cte_max_recursion_depth 上限 → “Recursive query aborted after N iterations”
--  ❌ 写 CYCLE 防环             → ERROR 1064（语法不支持）
--  ❌ 递归成员含聚合/GROUP BY/DISTINCT/WINDOW → ERROR 1140 / 1064
--  ❌ 递归成员对 CTE 引用多次   → ERROR 3676（recursive reference may appear once）
--  ❌ 多币种直接 SUM(amount_orig) → 跨币种相加，金额完全错误（必须 SUM(amount_usd)）
--  ❌ 万亿边在线递归            → 单线程迭代，无法扩展；应物化闭包表
-- =============================================================================
 
CREATE DATABASE IF NOT EXISTS JewelryAnalytics
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
 
-- 会话初始化（幂等，每次运行都设，确保环境一致）
SET SESSION cte_max_recursion_depth = 100000;   -- 递归深度上限调大（深树/深路径需要）
SET SESSION group_concat_max_len = 1000000;      -- 路径拼接可能很长
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0;
SET SESSION innodb_lock_wait_timeout = 50;
SET SESSION time_zone = '+00:00';                -- 全库以 UTC 存储时间
SET SESSION max_execution_time = 0;              -- 解除服务端单语句 30s 上限
 
-- =============================================================================
-- _r01_fw.sql  度量框架（递归主题专用，rec_ 前缀，复用 performance_schema 增量法）
-- =============================================================================
-- 说明：用 performance_schema.session_status 取四个计数器在“一条被测 SQL 前后”的
--       增量，量化递归/闭包查询的扫描、临时表、随机读代价，避免凭空吹“高性能”。
 
DROP TABLE IF EXISTS rec_demo_metric;
CREATE TABLE rec_demo_metric (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  label   VARCHAR(80) NOT NULL,
  ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  innodb_rows_read_d    BIGINT NOT NULL DEFAULT 0,
  created_tmp_tables_d  BIGINT NOT NULL DEFAULT 0,
  created_tmp_disk_tables_d BIGINT NOT NULL DEFAULT 0,
  handler_read_rnd_next_d  BIGINT NOT NULL DEFAULT 0,
  sort_rows_d          BIGINT NOT NULL DEFAULT 0
);
 
DROP TABLE IF EXISTS rec_demo_result;
CREATE TABLE rec_demo_result (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  section VARCHAR(40),
  note    VARCHAR(120),
  payload LONGTEXT,
  ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
DROP TABLE IF EXISTS rec_demo_probe;
CREATE TABLE rec_demo_probe (
  name          VARCHAR(60) NOT NULL PRIMARY KEY,
  caught_errno  INT NOT NULL DEFAULT 0,
  info          VARCHAR(255)
);
 
DROP TABLE IF EXISTS rec_demo_check;
CREATE TABLE rec_demo_check (
  vid     VARCHAR(20) NOT NULL PRIMARY KEY,
  passed  TINYINT NOT NULL,
  detail  VARCHAR(255)
);
 
DELIMITER //
 
-- 抓取当前四个计数器快照
DROP PROCEDURE IF EXISTS rec_snap//
CREATE PROCEDURE rec_snap()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS rec_snap_t;
  CREATE TEMPORARY TABLE rec_snap_t AS
  SELECT
    MAX(CASE WHEN VARIABLE_NAME='INNODB_ROWS_READ' THEN VARIABLE_VALUE ELSE 0 END) AS a,
    MAX(CASE WHEN VARIABLE_NAME='CREATED_TMP_TABLES' THEN VARIABLE_VALUE ELSE 0 END) AS b,
    MAX(CASE WHEN VARIABLE_NAME='CREATED_TMP_DISK_TABLES' THEN VARIABLE_VALUE ELSE 0 END) AS c,
    MAX(CASE WHEN VARIABLE_NAME='HANDLER_READ_RND_NEXT' THEN VARIABLE_VALUE ELSE 0 END) AS d,
    MAX(CASE WHEN VARIABLE_NAME='SORT_ROWS' THEN VARIABLE_VALUE ELSE 0 END) AS e
  FROM performance_schema.session_status
  WHERE VARIABLE_NAME IN
    ('INNODB_ROWS_READ','CREATED_TMP_TABLES','CREATED_TMP_DISK_TABLES',
     'HANDLER_READ_RND_NEXT','SORT_ROWS');
END//
 
-- 度量：执行一条 SQL（文本），把增量写入 rec_demo_metric
DROP PROCEDURE IF EXISTS rec_measure//
CREATE PROCEDURE rec_measure(IN p_label VARCHAR(80), IN p_sql TEXT)
BEGIN
  DECLARE v_a0,v_b0,v_c0,v_d0,v_e0 BIGINT DEFAULT 0;
  DECLARE v_a1,v_b1,v_c1,v_d1,v_e1 BIGINT DEFAULT 0;
  CALL rec_snap();
  SELECT a,b,c,d,e INTO v_a0,v_b0,v_c0,v_d0,v_e0 FROM rec_snap_t;
  SET @rec_sql = p_sql;
  PREPARE rec_stmt FROM @rec_sql;
  EXECUTE rec_stmt;
  DEALLOCATE PREPARE rec_stmt;
  CALL rec_snap();
  SELECT a,b,c,d,e INTO v_a1,v_b1,v_c1,v_d1,v_e1 FROM rec_snap_t;
  INSERT INTO rec_demo_metric
    (label, innodb_rows_read_d, created_tmp_tables_d, created_tmp_disk_tables_d,
     handler_read_rnd_next_d, sort_rows_d)
  VALUES
    (p_label, v_a1-v_a0, v_b1-v_b0, v_c1-v_c0, v_d1-v_d0, v_e1-v_e0);
END//
 
-- 自检断言：把结果写进 rec_demo_check（供 §12 汇总）
DROP PROCEDURE IF EXISTS rec_v//
CREATE PROCEDURE rec_v(IN p_vid VARCHAR(20), IN p_pass TINYINT, IN p_detail VARCHAR(255))
BEGIN
  INSERT INTO rec_demo_check (vid, passed, detail)
  VALUES (p_vid, p_pass, p_detail)
  ON DUPLICATE KEY UPDATE passed=p_pass, detail=p_detail;
END//
 
-- 探针：执行一条“预期会报错”的 SQL，捕获其错误号（证明 MySQL 不支持某语法）
DROP PROCEDURE IF EXISTS rec_err_probe//
CREATE PROCEDURE rec_err_probe(IN p_name VARCHAR(60), IN p_sql TEXT)
BEGIN
  DECLARE v_err INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 @en = MYSQL_ERRNO;
    SET v_err = @en;
    INSERT INTO rec_demo_probe (name, caught_errno, info)
    VALUES (p_name, v_err, LEFT(p_sql, 120))
    ON DUPLICATE KEY UPDATE caught_errno=v_err, info=LEFT(p_sql, 120);
  END;
  SET @rec_sql = p_sql;
  PREPARE rec_pst FROM @rec_sql;
  EXECUTE rec_pst;
  DEALLOCATE PREPARE rec_pst;
  -- 若没报错，也记录为 0（表示“语法被接受”，用于反证）
  INSERT INTO rec_demo_probe (name, caught_errno, info)
  VALUES (p_name, 0, LEFT(p_sql, 120))
  ON DUPLICATE KEY UPDATE caught_errno=0, info=LEFT(p_sql, 120);
END//
 
DELIMITER ;
 
-- =============================================================================
-- _r02_lab.sql  实验室：构建珠宝行业层级样本数据（HRM/ERP/MES/WMS/KMS/CRM）
--  全部为演示规模（合计约 1500 行），复用 agg_dim_country/agg_dim_currency。
--  幂等：仅当表为空时才造数；再次运行不重复插入。
-- =============================================================================
 
DELIMITER //
 
DROP PROCEDURE IF EXISTS rec_lab_build//
DROP PROCEDURE IF EXISTS rec_lab_reset//
 
CREATE PROCEDURE rec_lab_reset()
BEGIN
  DROP TABLE IF EXISTS rec_hrm_org, rec_erp_category, rec_mes_bom,
    rec_wms_location, rec_kms_tree, rec_crm_group;
END//
 
CREATE PROCEDURE rec_lab_build()
BEGIN
  -- -------------------------------------------------------------------------
  -- ① HRM 组织图（adjacency list：mgr_id 指向同表 emp_id，保证 mgr_id<emp_id 无环）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_hrm_org;
  CREATE TABLE rec_hrm_org (
    emp_id       INT PRIMARY KEY,
    mgr_id       INT NULL,
    emp_name     VARCHAR(40),
    dept         VARCHAR(20),
    country_code CHAR(2),
    currency_code CHAR(3),
    title        VARCHAR(40),
    hire_date    DATETIME,        -- 以 UTC 存储
    salary_usd   DECIMAL(12,2),
    amount_orig  DECIMAL(12,2)
  );
  CREATE INDEX ix_hrm_mgr ON rec_hrm_org (mgr_id);
 
  IF (SELECT COUNT(*) FROM rec_hrm_org) = 0 THEN
    SET @i = 1;
    WHILE @i <= 600 DO
      SET @cc = ELT(1 + (@i % 10), 'AE','AU','BR','CN','DE','FR','GB','IN','JP','US');
      SET @cur = CASE @cc WHEN 'AE' THEN 'AED' WHEN 'AU' THEN 'AUD' WHEN 'BR' THEN 'BRL'
                    WHEN 'CN' THEN 'CNY' WHEN 'DE' THEN 'EUR' WHEN 'FR' THEN 'EUR'
                    WHEN 'GB' THEN 'GBP' WHEN 'IN' THEN 'INR' WHEN 'JP' THEN 'JPY'
                    WHEN 'US' THEN 'USD' END;
      SET @rate = CASE @cc WHEN 'AE' THEN 0.27 WHEN 'AU' THEN 0.66 WHEN 'BR' THEN 0.18
                    WHEN 'CN' THEN 0.14 WHEN 'DE' THEN 1.08 WHEN 'FR' THEN 1.08
                    WHEN 'GB' THEN 1.27 WHEN 'IN' THEN 0.012 WHEN 'JP' THEN 0.0067
                    WHEN 'US' THEN 1.0 END;
      SET @mgr = CASE WHEN @i = 1 THEN NULL
                      ELSE 1 + FLOOR((@i - 2) / 5) END;   -- 每人最多 5 直接下属，深度≈log5
      INSERT INTO rec_hrm_org
        (emp_id, mgr_id, emp_name, dept, country_code, currency_code,
         title, hire_date, salary_usd, amount_orig)
      VALUES
        (@i, @mgr,
         CONCAT('Emp', @i),
         ELT(1 + (@i % 6), 'HR','Finance','Sales','Production','Logistics','IT'),
         @cc, @cur,
         ELT(1 + (@i % 5), 'Staff','Senior','Manager','Director','VP'),
         TIMESTAMPADD(DAY, (@i * 7) % 3000, '2015-01-01 08:00:00'),
         ROUND(3000 + ((@i * 37) % 9000), 2),
         ROUND((3000 + ((@i * 37) % 9000)) / @rate, 2));
      SET @i = @i + 1;
    END WHILE;
  END IF;
 
  -- -------------------------------------------------------------------------
  -- ② ERP 品类树（珠宝品类层级，硬编码，无环：parent_id < cat_id）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_erp_category;
  CREATE TABLE rec_erp_category (
    cat_id    INT PRIMARY KEY,
    parent_id INT NULL,
    cat_name  VARCHAR(40),
    cat_level INT
  );
  CREATE INDEX ix_erp_parent ON rec_erp_category (parent_id);
 
  IF (SELECT COUNT(*) FROM rec_erp_category) = 0 THEN
    INSERT INTO rec_erp_category (cat_id, parent_id, cat_name, cat_level) VALUES
    (1, NULL, 'Jewelry', 0),
    (2, 1, 'Necklace', 1), (3, 1, 'Ring', 1), (4, 1, 'Bracelet', 1),
    (5, 1, 'Earring', 1), (6, 1, 'Pendant', 1),
    (20, 2, 'Gold Necklace', 2), (21, 2, 'Diamond Necklace', 2), (22, 2, 'Pearl Necklace', 2),
    (30, 3, 'Solitaire Ring', 2), (31, 3, 'Wedding Band', 2), (32, 3, 'Signet Ring', 2),
    (40, 4, 'Bangle', 2), (41, 4, 'Charm Bracelet', 2),
    (50, 5, 'Stud Earring', 2), (51, 5, 'Drop Earring', 2),
    (60, 6, 'Gemstone Pendant', 2), (61, 6, 'Initial Pendant', 2),
    (200, 20, '18K Gold Necklace', 3), (201, 21, '1ct Diamond Necklace', 3),
    (300, 30, '1.5ct Solitaire', 3), (301, 31, 'Platinum Band', 3),
    (500, 50, 'Diamond Stud', 3), (600, 60, 'Sapphire Pendant', 3);
  END IF;
 
  -- -------------------------------------------------------------------------
  -- ③ MES 物料清单 BOM（多层级：成品 → 部件 → 原料，硬编码，无环）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_mes_bom;
  CREATE TABLE rec_mes_bom (
    bom_id      INT AUTO_INCREMENT PRIMARY KEY,
    parent_part VARCHAR(40),   -- 父件（装配体），NULL 表示根成品
    child_part  VARCHAR(40),   -- 子件（组件/原料）
    qty_per     DECIMAL(10,3), -- 父件每单位所需子件数量
    uom         VARCHAR(10)
  );
  CREATE INDEX ix_bom_parent ON rec_mes_bom (parent_part);
 
  IF (SELECT COUNT(*) FROM rec_mes_bom) = 0 THEN
    INSERT INTO rec_mes_bom (parent_part, child_part, qty_per, uom) VALUES
    -- L0 成品
    (NULL,            'Ring-18K-Diamond', 1,     'ea'),
    -- L1 成品拆解
    ('Ring-18K-Diamond', 'Gold-Casting',    1,    'ea'),
    ('Ring-18K-Diamond', 'Diamond-1ct',     1,    'ea'),
    ('Ring-18K-Diamond', 'Prong-Setting',   4,    'ea'),
    -- L2 Gold-Casting 拆解
    ('Gold-Casting',   'Gold-Bar-999',      3.5,  'g'),
    ('Gold-Casting',   'Alloy-Palladium',   0.4,  'g'),
    ('Gold-Casting',   'Molding-Fee',       1,    'op'),
    -- L2 Diamond-1ct 拆解
    ('Diamond-1ct',    'Rough-Diamond',     1,    'ea'),
    ('Diamond-1ct',    'Cutting-Fee',       1,    'op'),
    ('Diamond-1ct',    'Cert-Fee',          1,    'op'),
    -- L2 Prong-Setting 拆解
    ('Prong-Setting',  'Gold-Wire',         0.3,  'g'),
    ('Prong-Setting',  'Polish-Fee',        1,    'op'),
    -- L3 Gold-Bar-999 来源
    ('Gold-Bar-999',   'Refined-Gold',      1,    'g'),
    ('Gold-Bar-999',   'Smelting-Fee',      1,    'op');
  END IF;
 
  -- -------------------------------------------------------------------------
  -- ④ WMS 库位层级（DC → Zone → Shelf → Bin，程序化生成，无环）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_wms_location;
  CREATE TABLE rec_wms_location (
    loc_id   INT PRIMARY KEY,
    parent_id INT NULL,
    loc_name VARCHAR(60),
    loc_type VARCHAR(20),
    dc_code  VARCHAR(10)
  );
  CREATE INDEX ix_wms_parent ON rec_wms_location (parent_id);
 
  IF (SELECT COUNT(*) FROM rec_wms_location) = 0 THEN
    INSERT INTO rec_wms_location VALUES
      (1, NULL, 'Global DC Network', 'ROOT', 'GLOBAL'),
      (2, 1, 'HK Central DC', 'DC', 'HK'),
      (3, 1, 'CN Shenzhen DC', 'DC', 'CN'),
      (4, 1, 'US East DC', 'DC', 'US');
    SET @z = 5;
    WHILE @z <= 16 DO
      SET @dc = 2 + ((@z - 5) DIV 4);
      INSERT INTO rec_wms_location (loc_id, parent_id, loc_name, loc_type, dc_code)
      VALUES (@z, @dc, CONCAT(ELT(@dc-1,'HK','CN','US'),'-Z', (@z-5)%4+1), 'ZONE',
              ELT(@dc-1,'HK','CN','US'));
      SET @z = @z + 1;
    END WHILE;
    SET @sh = 17;
    WHILE @sh <= 76 DO
      SET @zp = 5 + ((@sh - 17) DIV 5);
      SET @dcv = (SELECT dc_code FROM rec_wms_location WHERE loc_id=@zp);
      INSERT INTO rec_wms_location (loc_id, parent_id, loc_name, loc_type, dc_code)
      VALUES (@sh, @zp, CONCAT('SH', @sh-16), 'SHELF', @dcv);
      SET @sh = @sh + 1;
    END WHILE;
    SET @bn = 77;
    WHILE @bn <= 676 DO
      SET @shp = 17 + ((@bn - 77) DIV 10);
      SET @dcv = (SELECT dc_code FROM rec_wms_location WHERE loc_id=@shp);
      INSERT INTO rec_wms_location (loc_id, parent_id, loc_name, loc_type, dc_code)
      VALUES (@bn, @shp, CONCAT('BIN', @bn-76), 'BIN', @dcv);
      SET @bn = @bn + 1;
    END WHILE;
  END IF;
 
  -- -------------------------------------------------------------------------
  -- ⑤ KMS 知识库树（分类 → 文档，硬编码，无环）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_kms_tree;
  CREATE TABLE rec_kms_tree (
    node_id   INT PRIMARY KEY,
    parent_id INT NULL,
    node_name VARCHAR(50),
    node_type VARCHAR(20)
  );
  CREATE INDEX ix_kms_parent ON rec_kms_tree (parent_id);
 
  IF (SELECT COUNT(*) FROM rec_kms_tree) = 0 THEN
    INSERT INTO rec_kms_tree (node_id, parent_id, node_name, node_type) VALUES
    (1, NULL, 'Knowledge Base', 'ROOT'),
    (10, 1, 'HR Policy', 'CAT'), (11, 1, 'Product Spec', 'CAT'),
    (12, 1, 'Tech Doc', 'CAT'), (13, 1, 'Compliance', 'CAT'),
    (100, 10, 'Leave Policy', 'DOC'), (101, 10, 'Payroll Guide', 'DOC'),
    (110, 11, 'Diamond Grading', 'DOC'), (111, 11, 'Gold Alloy Ratio', 'DOC'),
    (120, 12, 'MES Integration', 'DOC'), (121, 12, 'API Schema', 'DOC'),
    (130, 13, 'KYC Procedure', 'DOC'), (131, 13, 'Sanctions List', 'DOC');
  END IF;
 
  -- -------------------------------------------------------------------------
  -- ⑥ CRM 客户层级（母公司 → 子公司 → 门店，程序化，无环）
  -- -------------------------------------------------------------------------
  DROP TABLE IF EXISTS rec_crm_group;
  CREATE TABLE rec_crm_group (
    cust_id     INT PRIMARY KEY,
    parent_id   INT NULL,
    cust_name   VARCHAR(50),
    country_code CHAR(2)
  );
  CREATE INDEX ix_crm_parent ON rec_crm_group (parent_id);
 
  IF (SELECT COUNT(*) FROM rec_crm_group) = 0 THEN
    INSERT INTO rec_crm_group VALUES
      (1, NULL, 'Global Holdings Ltd', 'HK');
    SET @c = 2;
    WHILE @c <= 6 DO
      INSERT INTO rec_crm_group VALUES
        (@c, 1, CONCAT('Region ', @c), ELT(@c, 'CN','US','DE','JP','FR'));
      SET @c = @c + 1;
    END WHILE;
    SET @s = 7;
    WHILE @s <= 36 DO
      SET @p = 1 + ((@s - 7) DIV 6) + 1;   -- 归属某个 Region
      INSERT INTO rec_crm_group VALUES
        (@s, @p, CONCAT('Store ', @s), ELT(1 + (@s % 10),'AE','AU','BR','CN','DE','FR','GB','IN','JP','US'));
      SET @s = @s + 1;
    END WHILE;
  END IF;
 
END//
 
DELIMITER ;
 
-- =============================================================================
-- _r03_org.sql  HRM 组织图：递归 CTE 取“所有下属 / 向上链路 / 层级 / 路径”
-- =============================================================================
-- §3.1 向下展开：以 CEO(emp_id=1) 为根，递归取全部下属，附 depth 与路径
--   关键：锚点(anchor) 取根，递归成员 JOIN 自身 CTE；path 用 CONCAT 累积防重。
SELECT '--- §3.1 全公司组织树(片段) ---' AS sec;
WITH RECURSIVE org_tree AS (
  SELECT emp_id, mgr_id, emp_name, dept, 1 AS lvl,
         CAST(emp_id AS CHAR(1000)) AS path
  FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, e.mgr_id, e.emp_name, e.dept, t.lvl + 1,
         CONCAT(t.path, '>', e.emp_id)
  FROM rec_hrm_org e
  JOIN org_tree t ON e.mgr_id = t.emp_id
)
SELECT emp_id, mgr_id, emp_name, dept, lvl, path
FROM org_tree
ORDER BY lvl, emp_id
LIMIT 20;
 
-- §3.2 统计每个管理者的直接/间接下属数（组织规模）
SELECT '--- §3.2 各层级人数分布 ---' AS sec;
WITH RECURSIVE org_tree AS (
  SELECT emp_id, mgr_id, 1 AS lvl FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, e.mgr_id, t.lvl + 1
  FROM rec_hrm_org e JOIN org_tree t ON e.mgr_id = t.emp_id
)
SELECT lvl, COUNT(*) AS headcount
FROM org_tree GROUP BY lvl ORDER BY lvl;
 
-- §3.3 向上链路：取某员工(emp_id=600)到 CEO 的完整汇报链（面包屑）
SELECT '--- §3.3 员工600 的汇报链路(底->顶) ---' AS sec;
WITH RECURSIVE up_chain AS (
  SELECT emp_id, mgr_id, emp_name, title, 1 AS lvl
  FROM rec_hrm_org WHERE emp_id = 600
  UNION ALL
  SELECT e.emp_id, e.mgr_id, e.emp_name, e.title, u.lvl + 1
  FROM rec_hrm_org e
  JOIN up_chain u ON e.emp_id = u.mgr_id
)
SELECT emp_id, mgr_id, emp_name, title, lvl
FROM up_chain ORDER BY lvl;
 
-- =============================================================================
-- _r04_bom.sql  MES 物料清单 BOM：递归展开多层级，计算每层累计用量
-- =============================================================================
-- §4.1 BOM 爆炸（explosion）：从成品递归向下，cum_qty = 上层累计 × 本层单耗
--   这是“自顶向下乘算”，得到每件成品对各叶子原料的总需求。
SELECT '--- §4.1 BOM 爆炸：每件成品对各组件的总需求 ---' AS sec;
WITH RECURSIVE bom_exp AS (
  SELECT child_part, parent_part, qty_per, uom, 1 AS lvl, qty_per AS cum_qty
  FROM rec_mes_bom WHERE parent_part IS NULL          -- 根：成品
  UNION ALL
  SELECT b.child_part, b.parent_part, b.qty_per, b.uom, e.lvl + 1,
         ROUND(e.cum_qty * b.qty_per, 4)
  FROM rec_mes_bom b
  JOIN bom_exp e ON b.parent_part = e.child_part
)
SELECT child_part AS component, lvl AS bom_level,
       qty_per AS per_parent, uom, cum_qty AS per_finished
FROM bom_exp
ORDER BY lvl, child_part;
 
-- §4.2 仅看叶子原料（不再有子件者）：确认成品到原料的最深路径
SELECT '--- §4.2 叶子原料清单(无下级) ---' AS sec;
WITH RECURSIVE bom_exp AS (
  SELECT child_part, parent_part, 1 AS lvl, qty_per AS cum_qty
  FROM rec_mes_bom WHERE parent_part IS NULL
  UNION ALL
  SELECT b.child_part, b.parent_part, e.lvl + 1, ROUND(e.cum_qty * b.qty_per, 4)
  FROM rec_mes_bom b JOIN bom_exp e ON b.parent_part = e.child_part
)
SELECT child_part, lvl AS depth, cum_qty
FROM bom_exp
WHERE child_part NOT IN (SELECT parent_part FROM rec_mes_bom WHERE parent_part IS NOT NULL)
ORDER BY depth, child_part;
 
-- §4.3 单个组件(钻石)影响哪些上层成品路径（自底向上追溯）
SELECT '--- §4.3 追溯 Diamond-1ct 的上层装配 ---' AS sec;
WITH RECURSIVE up_bom AS (
  SELECT child_part, parent_part, 1 AS lvl
  FROM rec_mes_bom WHERE child_part = 'Diamond-1ct'
  UNION ALL
  SELECT b.child_part, b.parent_part, u.lvl + 1
  FROM rec_mes_bom b JOIN up_bom u ON b.child_part = u.parent_part
)
SELECT child_part, parent_part, lvl FROM up_bom ORDER BY lvl;
 
-- =============================================================================
-- _r05_category.sql  ERP 品类树：递归取所有后代 / 所有祖先(面包屑)
-- =============================================================================
-- §5.1 后代展开：以根 Jewelry(cat_id=1) 取整棵树
SELECT '--- §5.1 品类树(根=1 全部后代, 片段) ---' AS sec;
WITH RECURSIVE desc_cat AS (
  SELECT cat_id, parent_id, cat_name, cat_level, 1 AS depth
  FROM rec_erp_category WHERE cat_id = 1
  UNION ALL
  SELECT c.cat_id, c.parent_id, c.cat_name, c.cat_level, d.depth + 1
  FROM rec_erp_category c
  JOIN desc_cat d ON c.parent_id = d.cat_id
)
SELECT cat_id, parent_id, cat_name, cat_level, depth
FROM desc_cat ORDER BY depth, cat_id LIMIT 24;
 
-- §5.2 祖先面包屑：取某叶子(200=18K Gold Necklace)到根的路径
SELECT '--- §5.2 品类面包屑(200 -> 根) ---' AS sec;
WITH RECURSIVE anc_cat AS (
  SELECT cat_id, parent_id, cat_name, 1 AS depth
  FROM rec_erp_category WHERE cat_id = 200
  UNION ALL
  SELECT c.cat_id, c.parent_id, c.cat_name, a.depth + 1
  FROM rec_erp_category c
  JOIN anc_cat a ON c.cat_id = a.parent_id
)
SELECT cat_id, parent_id, cat_name, depth
FROM anc_cat ORDER BY depth DESC;
 
-- §5.3 统计每个品类下的直接+间接 SKU 数（子树规模）
SELECT '--- §5.3 各品类子树节点数 ---' AS sec;
WITH RECURSIVE desc_cat AS (
  SELECT cat_id, parent_id FROM rec_erp_category WHERE cat_id = 1
  UNION ALL
  SELECT c.cat_id, c.parent_id
  FROM rec_erp_category c JOIN desc_cat d ON c.parent_id = d.cat_id
)
SELECT (SELECT cat_name FROM rec_erp_category WHERE cat_id = 1) AS root,
       COUNT(*) AS total_nodes,
       MAX((SELECT cat_level FROM rec_erp_category WHERE cat_id=d.cat_id)) AS max_level
FROM desc_cat d;
 
-- =============================================================================
-- _r06_wms.sql  WMS 库位层级：递归取某 DC 下全部 Zone/Shelf/Bin 及其数量
-- =============================================================================
-- §6.1 以 HK DC(loc_id=2) 为根，递归展开整棵库位树，按类型聚合
SELECT '--- §6.1 HK DC 库位树类型分布 ---' AS sec;
WITH RECURSIVE loc_tree AS (
  SELECT loc_id, parent_id, loc_name, loc_type, dc_code, 1 AS depth
  FROM rec_wms_location WHERE loc_id = 2
  UNION ALL
  SELECT l.loc_id, l.parent_id, l.loc_name, l.loc_type, l.dc_code, t.depth + 1
  FROM rec_wms_location l
  JOIN loc_tree t ON l.parent_id = t.loc_id
)
SELECT loc_type, COUNT(*) AS cnt, MAX(depth) AS max_depth
FROM loc_tree GROUP BY loc_type ORDER BY FIELD(loc_type,'DC','ZONE','SHELF','BIN');
 
-- §6.2 取某个具体 Bin 到 DC 的库位路径（盘点/定位用）
SELECT '--- §6.2 Bin 定位路径(取第一个BIN) ---' AS sec;
WITH RECURSIVE loc_up AS (
  SELECT x.loc_id, x.parent_id, x.loc_name, x.loc_type, 1 AS depth
  FROM (SELECT loc_id, parent_id, loc_name, loc_type
        FROM rec_wms_location WHERE loc_type = 'BIN' LIMIT 1) x
  UNION ALL
  SELECT l.loc_id, l.parent_id, l.loc_name, l.loc_type, u.depth + 1
  FROM rec_wms_location l
  JOIN loc_up u ON l.loc_id = u.parent_id
)
SELECT loc_name, loc_type, depth FROM loc_up ORDER BY depth DESC;
 
-- §6.3 跨 DC 汇总：每个 DC 的 Bin 总数（递归按根分组）
SELECT '--- §6.3 各 DC 的库位分布 ---' AS sec;
WITH RECURSIVE loc_tree AS (
  SELECT loc_id, parent_id, loc_name, loc_type, dc_code, dc_code AS root_dc, 1 AS depth
  FROM rec_wms_location WHERE loc_type = 'DC'
  UNION ALL
  SELECT l.loc_id, l.parent_id, l.loc_name, l.loc_type, l.dc_code, t.root_dc, t.depth + 1
  FROM rec_wms_location l
  JOIN loc_tree t ON l.parent_id = t.loc_id
)
SELECT root_dc, COUNT(*) AS total_locs,
       SUM(loc_type='BIN') AS bins,
       SUM(loc_type='SHELF') AS shelves,
       SUM(loc_type='ZONE') AS zones
FROM loc_tree GROUP BY root_dc ORDER BY root_dc;
 
-- =============================================================================
-- _r07_cycle.sql  路径枚举 + 循环防护（MySQL 没有 SQL 标准 CYCLE 子句）
-- =============================================================================
-- §7.1 MySQL 不支持 CYCLE 子句：强行写会报 1064（记入探针，供 §12 汇总）
CALL rec_err_probe('no_cycle_clause',
  'WITH RECURSIVE c(cid, pid) AS (SELECT 1,0 UNION ALL SELECT pid, cid FROM c CYCLE cid SET is_cycle TO 1) SELECT 1 FROM c');
 
-- §7.2 手动循环防护：用 path 列 + FIND_IN_SET 阻止重复访问同一节点
--   构造一张含环(c=2<->c=3)的临时表，演示“无防护会死循环、有防护能终止”
DROP TABLE IF EXISTS rec_cyc;
CREATE TABLE rec_cyc (id INT, pid INT);
INSERT INTO rec_cyc VALUES (1, NULL), (2, 1), (3, 2), (2, 3);  -- 2<->3 形成环
 
SELECT '--- §7.2 带 FIND_IN_SET 防护(可终止) ---' AS sec;
WITH RECURSIVE guarded AS (
  SELECT id, pid, 1 AS lvl, CAST(id AS CHAR(200)) AS pth
  FROM rec_cyc WHERE id = 1
  UNION ALL
  SELECT c.id, c.pid, g.lvl + 1, CONCAT(g.pth, ',', c.id)
  FROM rec_cyc c
  JOIN guarded g ON c.pid = g.id
  WHERE NOT FIND_IN_SET(c.id, g.pth)        -- 关键：已访问过则不再下钻
)
SELECT id, pid, lvl, pth FROM guarded ORDER BY lvl, id;
 
-- §7.3 对比：若去掉防护，遇到 2<->3 会无限递归被 cte_max_recursion_depth 打断
--   （此处仅展示“危险写法”的注释，真正执行会报 aborted，不纳入 0 错误运行）
--   WITH RECURSIVE bad AS (
--     SELECT id, pid, 1 lvl FROM rec_cyc WHERE id=1
--     UNION ALL SELECT c.id, c.pid, b.lvl+1 FROM rec_cyc c JOIN bad b ON c.pid=b.id
--   ) SELECT * FROM bad;   -- 会在 depth 上限处中断
 
-- §7.4 路径枚举好处：一条 SQL 同时拿到“节点 + 完整祖先路径”，便于权限/归属判断
SELECT '--- §7.4 ERP 品类路径枚举(片段) ---' AS sec;
WITH RECURSIVE path_cat AS (
  SELECT cat_id, parent_id, cat_name, CAST(cat_name AS CHAR(500)) AS full_path
  FROM rec_erp_category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.cat_id, c.parent_id, c.cat_name, CONCAT(p.full_path, ' / ', c.cat_name)
  FROM rec_erp_category c
  JOIN path_cat p ON c.parent_id = p.cat_id
)
SELECT cat_id, cat_name, full_path
FROM path_cat WHERE parent_id IS NOT NULL
ORDER BY cat_id LIMIT 10;
 
-- =============================================================================
-- _r08_kms_crm.sql  覆盖剩余 4 域：KMS 知识树 / CRM 客户层级 / OA 审批链 / SCM 供应商层级
-- =============================================================================
-- §8.1 KMS 知识树：取某分类(Compliance=13)下的全部文档（后代展开）
SELECT '--- §8.1 KMS: Compliance 分类下全部文档 ---' AS sec;
WITH RECURSIVE kms_tree AS (
  SELECT node_id, parent_id, node_name, node_type, 1 AS depth
  FROM rec_kms_tree WHERE node_id = 13
  UNION ALL
  SELECT k.node_id, k.parent_id, k.node_name, k.node_type, t.depth + 1
  FROM rec_kms_tree k
  JOIN kms_tree t ON k.parent_id = t.node_id
)
SELECT node_id, node_name, node_type, depth
FROM kms_tree ORDER BY depth, node_id;
 
-- §8.2 CRM 客户层级：每个 Region 的子树规模与门店数（向下汇总）
SELECT '--- §8.2 CRM: 各 Region 子树规模 ---' AS sec;
WITH RECURSIVE crm_tree AS (
  SELECT cust_id, parent_id, cust_name, cust_name AS root_region
  FROM rec_crm_group WHERE cust_id BETWEEN 2 AND 6      -- 5 个 Region 为根
  UNION ALL
  SELECT c.cust_id, c.parent_id, c.cust_name, t.root_region
  FROM rec_crm_group c
  JOIN crm_tree t ON c.parent_id = t.cust_id
)
SELECT root_region,
       COUNT(*) AS subtree_nodes,
       SUM(cust_name LIKE 'Store%') AS stores
FROM crm_tree
GROUP BY root_region ORDER BY root_region;
 
-- §8.3 OA 审批链：用递归序列表达“申请人→经理→总监→VP→CEO”的逐级审批路由
SELECT '--- §8.3 OA: 审批路由链 ---' AS sec;
WITH RECURSIVE oa_chain AS (
  SELECT 1 AS step, 'Requester(Emp600)' AS role, 'Manager(Emp120)' AS next_role
  UNION ALL
  SELECT step + 1,
         next_role,
         CASE step WHEN 1 THEN 'Director(Emp30)'
                  WHEN 2 THEN 'VP(Emp6)'
                  WHEN 3 THEN 'CEO(Emp1)'
                  ELSE NULL END
  FROM oa_chain WHERE step < 4
)
SELECT step, role, next_role FROM oa_chain;
 
-- §8.4 SCM 供应商多级网络：品牌 -> 一级 -> 二级原料矿（内联边表 + 递归）
SELECT '--- §8.4 SCM: 供应商多级网络 ---' AS sec;
WITH RECURSIVE edges(supplier, child) AS (
  SELECT 'Lukfook', 'GoldRefinery' UNION ALL
  SELECT 'Lukfook', 'DiamondTrader' UNION ALL
  SELECT 'GoldRefinery', 'MineA' UNION ALL
  SELECT 'DiamondTrader', 'MineB' UNION ALL
  SELECT 'DiamondTrader', 'CuttingHouseC'
),
scm_tree AS (
  SELECT child AS node, supplier AS parent, 1 AS tier
  FROM edges WHERE supplier = 'Lukfook'
  UNION ALL
  SELECT e.child, e.supplier, t.tier + 1
  FROM edges e JOIN scm_tree t ON e.supplier = t.node
)
SELECT node, parent, tier FROM scm_tree ORDER BY tier, node;
 
-- =============================================================================
-- _r09_scale.sql  万亿级高并发：闭包表物化 / 索引 / 递归深度上限
-- =============================================================================
-- §9.1 痛点：递归 CTE 是“逐行迭代、单线程”，不能并行；对万亿边图在线递归不现实。
--   解法：ETL 一次性把传递闭包物化到 closure 表（祖先-后代-深度），查询变成索引点查。
 
DROP TABLE IF EXISTS rec_hrm_closure;
CREATE TABLE rec_hrm_closure (
  ancestor    INT NOT NULL,
  descendant INT NOT NULL,
  depth       INT NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  INDEX ix_desc (descendant)
);
 
-- 用递归 CTE 一次性计算整张组织闭包（自连接膨胀：每个祖先记录其所有后代）
INSERT INTO rec_hrm_closure (ancestor, descendant, depth)
WITH RECURSIVE rc AS (
  SELECT emp_id AS ancestor, emp_id AS descendant, 0 AS depth
  FROM rec_hrm_org
  UNION ALL
  SELECT rc.ancestor, e.emp_id, rc.depth + 1
  FROM rec_hrm_org e
  JOIN rc ON e.mgr_id = rc.descendant
)
SELECT ancestor, descendant, depth FROM rc;
 
-- §9.2 查询对比：CEO(1) 的所有下属
--   递归 CTE 版本（每次查询都迭代）
SELECT '--- §9.2a 递归CTE取CEO下属 ---' AS sec;
WITH RECURSIVE sub AS (
  SELECT emp_id, 1 AS lvl FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, s.lvl + 1 FROM rec_hrm_org e JOIN sub s ON e.mgr_id = s.emp_id
) SELECT COUNT(*) AS cte_subordinate_count FROM sub WHERE lvl > 0;
 
--   闭包表版本（索引点查，O(log n)）
SELECT '--- §9.2b 闭包表取CEO下属 ---' AS sec;
SELECT COUNT(*) AS closure_subordinate_count
FROM rec_hrm_closure WHERE ancestor = 1 AND depth > 0;
 
-- §9.3 度量：闭包点查 vs 递归 CTE 的扫描行数（演示索引点查更省）
CALL rec_measure('rec_closure_lookup',
  'SELECT COUNT(*) FROM rec_hrm_closure WHERE ancestor=1');
CALL rec_measure('rec_org_cte',
  'WITH RECURSIVE sub AS (SELECT emp_id,1 lvl FROM rec_hrm_org WHERE emp_id=1 UNION ALL SELECT e.emp_id,s.lvl+1 FROM rec_hrm_org e JOIN sub s ON e.mgr_id=s.emp_id) SELECT COUNT(*) FROM sub');
 
-- §9.4 cte_max_recursion_depth 上限探针：设极低后会“Recursive query aborted”
SET SESSION cte_max_recursion_depth = 2;
CALL rec_err_probe('depth_limit_abort',
  'WITH RECURSIVE t AS (SELECT emp_id,mgr_id,1 l FROM rec_hrm_org WHERE emp_id=1 UNION ALL SELECT e.emp_id,e.mgr_id,t.l+1 FROM rec_hrm_org e JOIN t ON e.mgr_id=t.emp_id) SELECT COUNT(*) FROM t');
SET SESSION cte_max_recursion_depth = 100000;   -- 还原
 
-- §9.5 索引才是递归 CTE 提速的关键：parent_id 上必须有索引（已建 ix_*_parent）
--   无索引时递归每步都是全表扫描；演示：对比有/无索引的递归扫描行（用度量）
CALL rec_measure('rec_wms_with_idx',
  'WITH RECURSIVE lt AS (SELECT loc_id,parent_id,1 d FROM rec_wms_location WHERE loc_id=2 UNION ALL SELECT l.loc_id,l.parent_id,lt.d+1 FROM rec_wms_location l JOIN lt ON l.parent_id=lt.loc_id) SELECT COUNT(*) FROM lt');
 
-- =============================================================================
-- _r10_international.sql  国际时区 + 多币种结算（HRM/全局薪酬）
-- =============================================================================
-- §10.1 时区：hire_date 以 UTC 存储，按国家固定偏移换算本地入职日
--   （本机 mysql.time_zone_name 空 → CONVERT_TZ 恒 NULL，故用 TIMESTAMPADD 硬编码偏移）
SELECT '--- §10.1 本地入职日(CN/BR/US 对照) ---' AS sec;
SELECT e.emp_id, e.country_code, e.hire_date AS utc_hire,
       DATE(TIMESTAMPADD(MINUTE, c.offset_min, e.hire_date)) AS local_hire_day
FROM rec_hrm_org e
JOIN agg_dim_country c ON c.country_code = e.country_code
WHERE e.country_code IN ('CN','BR','US')
ORDER BY e.emp_id LIMIT 12;
 
-- §10.2 各国家按“本地入职年”统计人数（时区把边界日的归属年可能改变）
SELECT '--- §10.2 按本地入职年的人数分布 ---' AS sec;
SELECT e.country_code,
       YEAR(TIMESTAMPADD(MINUTE, c.offset_min, e.hire_date)) AS local_hire_year,
       COUNT(*) AS hires
FROM rec_hrm_org e
JOIN agg_dim_country c ON c.country_code = e.country_code
GROUP BY e.country_code, local_hire_year
ORDER BY e.country_code, local_hire_year;
 
-- §10.3 多币种结算：跨国薪酬汇总 —— 必须 SUM(salary_usd)，绝不可 SUM(amount_orig)
SELECT '--- §10.3 各国薪酬(正确USD vs 错误ORIG) ---' AS sec;
SELECT country_code,
       COUNT(*) AS headcount,
       ROUND(SUM(salary_usd), 2)   AS comp_usd_correct,
       ROUND(SUM(amount_orig), 2)  AS comp_orig_WRONG
FROM rec_hrm_org
GROUP BY country_code
ORDER BY comp_usd_correct DESC;
 
-- §10.4 全球薪酬总计：单币种归一（唯一正确口径）
SELECT '--- §10.4 全球薪酬总计(USD) ---' AS sec;
SELECT ROUND(SUM(salary_usd), 2) AS global_comp_usd
FROM rec_hrm_org;
 
-- =============================================================================
-- _r11_verify.sql  自检 V1–V12（全部数据驱动，0 错误运行前提下的断言）
-- =============================================================================
TRUNCATE TABLE rec_demo_check;
 
-- V1：递归 CTE 取出整张组织图（600 人全在）
SET @v1 = (WITH RECURSIVE t AS (
  SELECT emp_id, 1 AS l FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, t.l + 1 FROM rec_hrm_org e JOIN t ON e.mgr_id = t.emp_id
) SELECT COUNT(*) FROM t);
CALL rec_v('V1_org_all', IF(@v1=600,1,0), CONCAT('org_nodes=',@v1));
 
-- V2：组织最大深度在合理区间（本公式约 4 层）
SET @v2 = (WITH RECURSIVE t AS (
  SELECT emp_id, 1 AS l FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, t.l + 1 FROM rec_hrm_org e JOIN t ON e.mgr_id = t.emp_id
) SELECT MAX(l) FROM t);
CALL rec_v('V2_org_depth', IF(@v2>=3 AND @v2<=6,1,0), CONCAT('max_depth=',@v2));
 
-- V3：BOM 爆炸后节点数 = 全部 14 行（树形，每边出现一次）
SET @v3 = (WITH RECURSIVE b AS (
  SELECT child_part, parent_part, qty_per, 1 AS l, qty_per AS c
  FROM rec_mes_bom WHERE parent_part IS NULL
  UNION ALL
  SELECT m.child_part, m.parent_part, m.qty_per, b.l + 1, ROUND(b.c * m.qty_per, 4)
  FROM rec_mes_bom m JOIN b ON m.parent_part = b.child_part
) SELECT COUNT(*) FROM b);
CALL rec_v('V3_bom_nodes', IF(@v3=14,1,0), CONCAT('bom_nodes=',@v3));
 
-- V4：MySQL 不支持 CYCLE 子句 -> 探针捕获 1064
SET @v4 = (SELECT caught_errno FROM rec_demo_probe WHERE name='no_cycle_clause');
CALL rec_v('V4_no_cycle', IF(@v4=1064,1,0), CONCAT('errno=',@v4));
 
-- V5：FIND_IN_SET 循环防护可终止（含环表只得到 3 个可达节点，否则死循环）
SET @v5 = (WITH RECURSIVE g AS (
  SELECT id, pid, 1 AS l, CAST(id AS CHAR(200)) AS p FROM rec_cyc WHERE id = 1
  UNION ALL
  SELECT c.id, c.pid, g.l + 1, CONCAT(g.p, ',', c.id)
  FROM rec_cyc c JOIN g ON c.pid = g.id
  WHERE NOT FIND_IN_SET(c.id, g.p)
) SELECT COUNT(*) FROM g);
CALL rec_v('V5_cycle_guard', IF(@v5=3,1,0), CONCAT('guarded_rows=',@v5));
 
-- V6：ERP 品类树（根=1）后代 = 全部 24 节点
SET @v6 = (WITH RECURSIVE d AS (
  SELECT cat_id, parent_id, 1 AS l FROM rec_erp_category WHERE cat_id = 1
  UNION ALL
  SELECT c.cat_id, c.parent_id, d.l + 1 FROM rec_erp_category c JOIN d ON c.parent_id = d.cat_id
) SELECT COUNT(*) FROM d);
CALL rec_v('V6_erp_tree', IF(@v6=24,1,0), CONCAT('cat_nodes=',@v6));
 
-- V7：HK DC(loc_id=2) 子树 = 1 DC + 4 Zone + 20 Shelf + 200 Bin = 225
SET @v7 = (WITH RECURSIVE t AS (
  SELECT loc_id, parent_id, loc_type, 1 AS l FROM rec_wms_location WHERE loc_id = 2
  UNION ALL
  SELECT l.loc_id, l.parent_id, l.loc_type, t.l + 1
  FROM rec_wms_location l JOIN t ON l.parent_id = t.loc_id
) SELECT COUNT(*) FROM t);
CALL rec_v('V7_wms_hk', IF(@v7=225,1,0), CONCAT('hk_locs=',@v7));
 
-- V8：多币种结算错误口径（orig 合计 ≠ usd 合计），必须取 usd
SET @v8u = (SELECT ROUND(SUM(salary_usd),2) FROM rec_hrm_org);
SET @v8o = (SELECT ROUND(SUM(amount_orig),2) FROM rec_hrm_org);
CALL rec_v('V8_currency', IF(@v8u<>@v8o AND @v8u>0,1,0), CONCAT('usd=',@v8u,' orig=',@v8o));
 
-- V9：闭包表(含自环)节点数 == 递归 CTE(含根)节点数（= 600），证明物化正确
--   注：闭包按设计含 (ancestor=descendant, depth=0) 自环；递归 CTE 锚点含 CEO 根。
SET @v9c = (SELECT COUNT(*) FROM rec_hrm_closure WHERE ancestor=1);
SET @v9t = (WITH RECURSIVE s AS (
  SELECT emp_id, 1 AS l FROM rec_hrm_org WHERE emp_id = 1
  UNION ALL
  SELECT e.emp_id, s.l + 1 FROM rec_hrm_org e JOIN s ON e.mgr_id = s.emp_id
) SELECT COUNT(*) FROM s);
CALL rec_v('V9_closure_eq_cte', IF(@v9c=@v9t AND @v9c=600,1,0), CONCAT('closure=',@v9c,' cte=',@v9t));
 
-- V10：cte_max_recursion_depth 上限会被强制执行（探针捕获非零错误号）
SET @v10 = (SELECT caught_errno FROM rec_demo_probe WHERE name='depth_limit_abort');
CALL rec_v('V10_depth_limit', IF(@v10<>0,1,0), CONCAT('errno=',@v10));
 
-- V11：KMS Compliance(13) 子树 = 13 + 130 + 131 = 3
SET @v11 = (WITH RECURSIVE k AS (
  SELECT node_id, parent_id, 1 AS l FROM rec_kms_tree WHERE node_id = 13
  UNION ALL
  SELECT n.node_id, n.parent_id, k.l + 1 FROM rec_kms_tree n JOIN k ON n.parent_id = k.node_id
) SELECT COUNT(*) FROM k);
CALL rec_v('V11_kms', IF(@v11=3,1,0), CONCAT('kms_nodes=',@v11));
 
-- V12：样本数据完整存在（HRM 600 / WMS 676）
SET @v12a = (SELECT COUNT(*) FROM rec_hrm_org);
SET @v12b = (SELECT COUNT(*) FROM rec_wms_location);
CALL rec_v('V12_data_present', IF(@v12a=600 AND @v12b=676,1,0), CONCAT('hrm=',@v12a,' wms=',@v12b));
 
-- §11.9 汇总裁决
SELECT CONCAT('PASS=', SUM(passed=1), '  FAIL=', SUM(passed=0)) AS verdict
FROM rec_demo_check;
SELECT vid, passed, detail FROM rec_demo_check ORDER BY vid;
 
-- =============================================================================
-- _r12_summary.sql  总结：递归能力对照表 / 度量 / 结论 / 方法 / 脚本结构
-- =============================================================================
-- §12.1 MySQL 递归能力对照表（本机 9.0.1 实测）
SELECT '能力' AS item, 'MySQL 9.0 表现' AS behavior, '备注' AS note UNION ALL
SELECT 'WITH RECURSIVE', '✅ 必须显式写 RECURSIVE', '漏写 → ERROR 1146' UNION ALL
SELECT '自引用(锚点+递归)', '✅ 支持', '递归成员对 CTE 仅可引用一次' UNION ALL
SELECT 'CYCLE 防环子句', '❌ ERROR 1064', '须手动 FIND_IN_SET 路径防护' UNION ALL
SELECT '递归成员聚合/GROUP BY', '❌ ERROR 1140/1064', '聚合需放到外层' UNION ALL
SELECT 'cte_max_recursion_depth', '⚠ 默认 1000', '深树需调大，超限报 aborted' UNION ALL
SELECT '闭包/路径枚举物化', '✅ 用递归 CTE 一次性建', '万亿级查询靠物化表' UNION ALL
SELECT '多币种 SUM(orig)', '❌ 跨币种相加错误', '只能 SUM(amount_usd)';
 
-- §12.2 度量表（§9 实测：闭包点查 vs 递归 CTE 扫描行）
SELECT label, innodb_rows_read_d AS rows_read, sort_rows_d AS sort_rows,
       created_tmp_tables_d AS tmp_tables
FROM rec_demo_metric
WHERE label IN ('rec_closure_lookup','rec_org_cte','rec_wms_with_idx')
ORDER BY label;
 
-- §12.3 三条核心结论
--  ① 递归 CTE 适合“一次性/中小图”的层级检索原型；但它是单线程迭代、不可并行，
--     对万亿边图在线递归不现实——正确姿势是 ETL 把传递闭包物化到 closure 表，查询变索引点查。
--  ② 有环数据必须手动防环（FIND_IN_SET 路径判重），否则 cte_max_recursion_depth 触发中断；
--     MySQL 没有 SQL 标准的 CYCLE 子句（写即 1064）。
--  ③ 深度受 cte_max_recursion_depth 限制，深组织架构/深 BOM 务必调大；
--     多币种层级汇总只能 SUM(amount_usd) 归一，绝不可 SUM(本币 amount_orig)。
 
-- §12.4 八条工程方法清单
--   1) 邻接表(parent_id) + 递归 CTE 取子树/祖先链/路径枚举；
--   2) BOM 爆炸用 cum_qty = 上层累计 × 本层单耗，自顶向下乘算；
--   3) 防环：path 列 + FIND_IN_SET 判重，或 closure 物化时自然去环；
--   4) 万亿级：closure 表(ancestor,descendant,depth) + 双索引，查询 O(log n)；
--   5) parent_id 上必须建索引，否则递归每步全表扫描；
--   6) 调大 cte_max_recursion_depth 支持深树；
--   7) 时区：TIMESTAMPADD(固定偏移) 换算本地日（CONVERT_TZ 本机恒 NULL）；
--   8) 多币种：只 SUM(amount_usd)，结算用单币种归一。
 
-- §12.5 脚本结构（13 分片）
SELECT '分片' AS shard, '内容' AS content UNION ALL
SELECT '_r00_head', '运行必读/能力差异/反模式/SET SESSION' UNION ALL
SELECT '_r01_fw', '度量框架 rec_snap/measure/v/err_probe' UNION ALL
SELECT '_r02_lab', 'rec_lab_build 建 6 张层级表(演示规模)' UNION ALL
SELECT '_r03_org', 'HRM 组织图:下属/链路/深度/路径' UNION ALL
SELECT '_r04_bom', 'MES BOM 爆炸与累计用量' UNION ALL
SELECT '_r05_category', 'ERP 品类树 后代/祖先' UNION ALL
SELECT '_r06_wms', 'WMS 库位层级 DC>Zone>Shelf>Bin' UNION ALL
SELECT '_r07_cycle', '路径枚举 + CYCLE 不支持 + 防环' UNION ALL
SELECT '_r08_kms_crm', 'KMS/CRM/OA/SCM 四域递归' UNION ALL
SELECT '_r09_scale', '闭包表物化/索引/深度上限' UNION ALL
SELECT '_r10_international', '时区本地日 + 多币种结算' UNION ALL
SELECT '_r11_verify', 'V1–V12 自检' UNION ALL
SELECT '_r12_summary', '对照表/度量/结论/方法/结构';
 
-- §12.6 终裁
SELECT CONCAT('FINAL: PASS=', SUM(passed=1), '  FAIL=', SUM(passed=0))
       AS final_verdict FROM rec_demo_check;
 
-- 清理本主题演示用的临时辅助表
DROP TABLE IF EXISTS rec_cyc;