sql:Implementing Full-Text Search in SQL using sql server 2025
/* ================================================================================
递归查询与层级检索实战 (Recursive Queries and Hierarchical Retrieval)
目标环境：SQL Server 2025 / 兼容级别 170
业务场景：国际化珠宝集团的 ERP / CRM / HR 三域
---------------------------------------------------------------------------
四个真实层级场景
HR 组织架构树 集团 -> 大区 -> 国家/地区 -> 城市 -> 门店 / 职能中心 -> 小组
ERP 商品品类树 全部商品 -> 珠宝首饰 -> 戒指 -> 钻石戒指 -> 单钻戒指
ERP BOM 物料清单 成品戒指 -> 戒托总成 -> 镶嵌托架 -> 爪镶件 -> 镶爪料
CRM 推荐人层级 会员 -> 直接推荐 -> 二级推荐 -> 三级推荐 ...
---------------------------------------------------------------------------
本脚本演示的五个层次
01 同一棵树的五种存储模型（邻接表 / 物化路径 / 闭包表 / 嵌套集 / hierarchyid）
02 递归 CTE 基础：组织架构上下行遍历与层级汇总
03 递归 CTE 进阶：商品品类树 + 自底向上 rollup
04 递归 CTE 硬核：BOM 用量爆炸、成本卷积、where-used、环检测
05 CRM 推荐人层级：多级下线、分佣链路
06 递归 CTE 的硬限制（实测矩阵，不是抄文档）
07 万亿级高并发：层级维表 + 事实表关联的实测对比与选型矩阵
---------------------------------------------------------------------------
环境与坑（实测确认）
· 脚本文件必须是无 BOM 的 UTF-8
· sqlcmd 输出重定向到文件时 o:65001 失效，日志会落成 GBK
-> 用 -h -1 -W 纯文本模式，或事后 Python 转码
· 递归 CTE 的「递归成员」限制（LEFT JOIN / TOP / DISTINCT / 聚合 /
GROUP BY）是【编译期绑定错误】，静态批次里 TRY/CATCH 捕获不到，
必须包进动态 SQL 才能捕获 —— 见第 06 节
脚本可重复执行（先 DROP 再建）
================================================================================ */
SET NOCOUNT ON;
GO
-- 筛选索引 / 索引视图 / 计算列索引 都要求这一组 SET 选项为 ON。
-- sqlcmd 默认把 QUOTED_IDENTIFIER 关掉，不显式打开会报 1934（SSMS 里默认是开的）。
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
GO
USE master;
GO
IF DB_ID(N'JewelryAnalytics') IS NULL
THROW 50001, N'未找到 JewelryAnalytics 演示库，请先执行 SQLServer2025_WindowFunctions_JewelryDemo.sql', 1;
GO
USE JewelryAnalytics;
GO
PRINT N'===== 00 环境准备 =====';
DECLARE @compatLvl INT = (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME());
PRINT N'SQL Server 版本: ' + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50))
+ N' / 兼容级别: ' + CAST(@compatLvl AS NVARCHAR(5));
GO
-- 快照隔离：让报表读不阻塞夜批写（层级维表是读多写极少，这条很关键）
IF NOT EXISTS (SELECT 1 FROM sys.databases
WHERE name = DB_NAME() AND snapshot_isolation_state = 1 AND is_read_committed_snapshot_on = 1)
BEGIN
ALTER DATABASE JewelryAnalytics SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE JewelryAnalytics SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE JewelryAnalytics SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE JewelryAnalytics SET MULTI_USER;
PRINT N'已开启 ALLOW_SNAPSHOT_ISOLATION + READ_COMMITTED_SNAPSHOT';
END
ELSE
PRINT N'快照隔离已就绪';
GO
SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model_desc
FROM sys.databases WHERE name = DB_NAME();
GO
/* ================================================================================
01 同一棵树的五种存储模型
---------------------------------------------------------------------------
题目：HR 组织架构树。同一批节点，用五种经典方案各存一份，
然后对「读子树 / 读祖先链 / 插入节点 / 移动子树」四种操作两两对比。
为什么值得花这一节：层级数据没有「一种最优解」，只有「读写比决定了选哪个」。
珠宝集团的实际情况是：组织架构几个月才动一次，但每周有几百次报表按组织汇总
-> 典型的读多写极少 -> 闭包表 / 物化路径胜出。
================================================================================ */
PRINT N'';
PRINT N'===== 01 五种层级存储模型 =====';
GO
DROP TABLE IF EXISTS hr.OrgUnitClosure;
DROP TABLE IF EXISTS hr.OrgUnitNestedSet;
DROP TABLE IF EXISTS hr.OrgUnitHid;
DROP TABLE IF EXISTS hr.OrgUnit;
GO
-- ---------------------------------------------------------------------------
-- 1.1 模型一：邻接表 (Adjacency List) —— 只存 ParentId
-- 这是最自然、写入最便宜的模型，也是几乎所有 ORM 的默认做法
-- 顺带在同一张表上冗余 Lvl / Path，这就是「邻接表 + 物化路径」混合方案
-- ---------------------------------------------------------------------------
CREATE TABLE hr.OrgUnit (
OrgUnitId INT IDENTITY(1,1) NOT NULL,
OrgCode NVARCHAR(20) NOT NULL,
OrgName NVARCHAR(60) NOT NULL,
OrgType NVARCHAR(12) NOT NULL, -- 集团/大区/国家/地区/城市/门店/职能中心/职能部门/小组
ParentId INT NULL, -- 邻接表的全部奥义就在这一列
CountryCode CHAR(2) NULL,
StoreCode VARCHAR(8) NULL, -- 与 erp.Store 对齐
SortOrder INT NOT NULL CONSTRAINT DF_OrgUnit_Sort DEFAULT (10),
Lvl TINYINT NULL, -- 冗余：层级（根=1）
Path NVARCHAR(400) NULL, -- 冗余：物化路径 /1/12/37/
IsLeaf BIT NOT NULL CONSTRAINT DF_OrgUnit_Leaf DEFAULT (0),
ValidFrom DATE NOT NULL CONSTRAINT DF_OrgUnit_VF DEFAULT ('2020-01-01'),
CONSTRAINT PK_OrgUnit PRIMARY KEY CLUSTERED (OrgUnitId),
CONSTRAINT UQ_OrgUnit_Code UNIQUE (OrgCode),
CONSTRAINT FK_OrgUnit_Parent FOREIGN KEY (ParentId) REFERENCES hr.OrgUnit(OrgUnitId)
);
GO
CREATE INDEX IX_OrgUnit_Parent ON hr.OrgUnit (ParentId) INCLUDE (OrgName, OrgType, Lvl);
CREATE INDEX IX_OrgUnit_Path ON hr.OrgUnit (Path);
CREATE UNIQUE INDEX UQ_OrgUnit_Store ON hr.OrgUnit (StoreCode) WHERE StoreCode IS NOT NULL;
GO
-- ---------------------------------------------------------------------------
-- 1.2 模型二：闭包表 (Closure Table) —— 把「所有祖孙对」显式存下来
-- 行数 = 节点数 x 平均深度。组织树几千节点、深度 5-6 层，闭包表也就几万行，
-- 完全可以常驻内存。这是生产环境我最推荐的方案。
-- ---------------------------------------------------------------------------
CREATE TABLE hr.OrgUnitClosure (
AncestorId INT NOT NULL,
DescendantId INT NOT NULL,
Depth INT NOT NULL, -- 0 = 自己到自己的自反对
CONSTRAINT PK_OrgUnitClosure PRIMARY KEY CLUSTERED (AncestorId, DescendantId),
CONSTRAINT FK_OC_A FOREIGN KEY (AncestorId) REFERENCES hr.OrgUnit(OrgUnitId),
CONSTRAINT FK_OC_D FOREIGN KEY (DescendantId) REFERENCES hr.OrgUnit(OrgUnitId)
);
GO
CREATE INDEX IX_OrgUnitClosure_Desc ON hr.OrgUnitClosure (DescendantId, AncestorId, Depth);
GO
-- ---------------------------------------------------------------------------
-- 1.3 模型三：嵌套集 (Nested Set) —— 用 Lft/Rgt 区间表示子树
-- 读子树是 O(1) 的 BETWEEN，写是 O(N) 的区间平移。
-- 只有在「几乎从不写」的场景才值得。
-- ---------------------------------------------------------------------------
CREATE TABLE hr.OrgUnitNestedSet (
OrgUnitId INT NOT NULL,
Lft INT NOT NULL,
Rgt INT NOT NULL,
Lvl INT NOT NULL,
CONSTRAINT PK_OrgUnitNestedSet PRIMARY KEY CLUSTERED (OrgUnitId),
CONSTRAINT FK_ONS_Org FOREIGN KEY (OrgUnitId) REFERENCES hr.OrgUnit(OrgUnitId)
);
GO
CREATE UNIQUE INDEX UQ_ONS_Lft ON hr.OrgUnitNestedSet (Lft);
CREATE UNIQUE INDEX UQ_ONS_Rgt ON hr.OrgUnitNestedSet (Rgt);
GO
-- ---------------------------------------------------------------------------
-- 1.4 模型四：hierarchyid（SQL Server 原生 CLR 类型）
-- 微软已把它标为 "not recommended for new development"，但存量系统常见。
-- 它能很优雅地做「取第 N 层祖先」「判断包含关系」。
-- ---------------------------------------------------------------------------
CREATE TABLE hr.OrgUnitHid (
OrgUnitId INT NOT NULL,
NodePath HIERARCHYID NOT NULL,
Lvl INT NOT NULL, -- 冗余：hierarchyid 的 GetLevel() 不能用做计算列
CONSTRAINT PK_OrgUnitHid PRIMARY KEY CLUSTERED (OrgUnitId),
CONSTRAINT FK_OH_Org FOREIGN KEY (OrgUnitId) REFERENCES hr.OrgUnit(OrgUnitId)
);
GO
CREATE UNIQUE INDEX UQ_OH_NodePath ON hr.OrgUnitHid (NodePath);
GO
-- ---------------------------------------------------------------------------
-- 1.5 灌入组织架构数据（与 dbo.Country / erp.Store 真实取值对齐）
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS #OrgSeed;
CREATE TABLE #OrgSeed (
Seq INT IDENTITY(1,1),
OrgCode NVARCHAR(20) NOT NULL,
OrgName NVARCHAR(60) NOT NULL,
OrgType NVARCHAR(12) NOT NULL,
ParentCode NVARCHAR(20) NULL,
CountryCode CHAR(2) NULL,
StoreCode VARCHAR(8) NULL,
SortOrder INT NOT NULL
);
INSERT INTO #OrgSeed (OrgCode, OrgName, OrgType, ParentCode, CountryCode, StoreCode, SortOrder) VALUES
-- 集团
(N'GRP', N'某集团', N'集团', NULL, NULL, NULL, 10),
-- 大区（与 dbo.Country.Region 口径一致）
(N'R-GCN', N'大中华区', N'大区', N'GRP', NULL, NULL, 10),
(N'R-SEA', N'东南亚区', N'大区', N'GRP', NULL, NULL, 20),
(N'R-EA', N'东亚区', N'大区', N'GRP', NULL, NULL, 30),
(N'R-EU', N'欧洲区', N'大区', N'GRP', NULL, NULL, 40),
(N'R-ME', N'中东区', N'大区', N'GRP', NULL, NULL, 50),
(N'R-NA', N'北美区', N'大区', N'GRP', NULL, NULL, 60),
-- 国家 / 地区
(N'C-HK', N'中国香港', N'国家/地区', N'R-GCN', 'HK', NULL, 10),
(N'C-CN', N'中国', N'国家/地区', N'R-GCN', 'CN', NULL, 20),
(N'C-SG', N'新加坡', N'国家/地区', N'R-SEA', 'SG', NULL, 10),
(N'C-JP', N'日本', N'国家/地区', N'R-EA', 'JP', NULL, 10),
(N'C-FR', N'法国', N'国家/地区', N'R-EU', 'FR', NULL, 10),
(N'C-AE', N'阿联酋', N'国家/地区', N'R-ME', 'AE', NULL, 10),
(N'C-US', N'美国', N'国家/地区', N'R-NA', 'US', NULL, 10),
-- 城市 / 商圈
(N'Y-HK', N'中国香港核心商圈', N'城市', N'C-HK', 'HK', NULL, 10),
(N'Y-HK-A', N'港岛东区（待启用）', N'城市', N'C-HK', 'HK', NULL, 20),
(N'Y-SHZ', N'深圳区域', N'城市', N'C-CN', 'CN', NULL, 10),
(N'Y-SHA', N'上海区域', N'城市', N'C-CN', 'CN', NULL, 20),
(N'Y-BJ', N'北京区域', N'城市', N'C-CN', 'CN', NULL, 30),
(N'Y-SG', N'新加坡区域', N'城市', N'C-SG', 'SG', NULL, 10),
(N'Y-TYO', N'东京区域', N'城市', N'C-JP', 'JP', NULL, 10),
(N'Y-PAR', N'巴黎区域', N'城市', N'C-FR', 'FR', NULL, 10),
(N'Y-DXB', N'迪拜区域', N'城市', N'C-AE', 'AE', NULL, 10),
(N'Y-NYC', N'纽约区域', N'城市', N'C-US', 'US', NULL, 10),
-- 门店（名称与编码直接取自 erp.Store）
(N'S-HK-CEN', N'中环皇后大道店', N'门店', N'Y-HK', 'HK', 'HK-CEN', 10),
(N'S-HK-TST', N'尖沙咀海港城店', N'门店', N'Y-HK', 'HK', 'HK-TST', 20),
(N'S-SHZ', N'深圳万象城店', N'门店', N'Y-SHZ', 'CN', 'CN-SHZ', 10),
(N'S-SHA', N'上海南京西路旗舰店', N'门店', N'Y-SHA', 'CN', 'CN-SHA', 10),
(N'S-BJ', N'北京国贸店', N'门店', N'Y-BJ', 'CN', 'CN-BJ', 10),
(N'S-SG', N'乌节路 ION 店', N'门店', N'Y-SG', 'SG', 'SG-ORC', 10),
(N'S-GIN', N'银座中央通店', N'门店', N'Y-TYO', 'JP', 'JP-GIN', 10),
(N'S-PAR', N'巴黎旺多姆广场店', N'门店', N'Y-PAR', 'FR', 'FR-PAR', 10),
(N'S-DXB', N'迪拜购物中心店', N'门店', N'Y-DXB', 'AE', 'AE-DXB', 10),
(N'S-NYC', N'纽约第五大道店', N'门店', N'Y-NYC', 'US', 'US-NYC', 10),
-- 职能线（一条与地理线平行的层级分支，用来验证多根/多分支）
(N'F-HQ', N'集团职能中心', N'职能中心', N'GRP', NULL, NULL, 90),
(N'F-FIN', N'财务共享中心', N'职能部门', N'F-HQ', NULL, NULL, 10),
(N'F-HR', N'人力资源中心', N'职能部门', N'F-HQ', NULL, NULL, 20),
(N'F-SCM', N'商品与供应链中心', N'职能部门', N'F-HQ', NULL, NULL, 30),
(N'F-MKT', N'品牌与市场中心', N'职能部门', N'F-HQ', NULL, NULL, 40),
(N'F-IT', N'数字化技术中心', N'职能部门', N'F-HQ', NULL, NULL, 50),
(N'F-ATL', N'设计与工坊中心', N'职能部门', N'F-HQ', NULL, NULL, 60),
(N'F-FIN-1', N'资金与外汇结算组', N'小组', N'F-FIN', NULL, NULL, 10),
(N'F-FIN-2', N'税务与合规组', N'小组', N'F-FIN', NULL, NULL, 20),
(N'F-HR-1', N'薪酬与绩效组', N'小组', N'F-HR', NULL, NULL, 10),
(N'F-HR-2', N'全球招聘组', N'小组', N'F-HR', NULL, NULL, 20),
(N'F-SCM-1', N'钻石与宝石采购组', N'小组', N'F-SCM', NULL, NULL, 10),
(N'F-SCM-2', N'贵金属与料件组', N'小组', N'F-SCM', NULL, NULL, 20),
(N'F-SCM-3', N'中央仓与配送组', N'小组', N'F-SCM', NULL, NULL, 30),
(N'F-MKT-1', N'门店视觉与陈列组', N'小组', N'F-MKT', NULL, NULL, 10),
(N'F-MKT-2', N'会员与私域组', N'小组', N'F-MKT', NULL, NULL, 20),
(N'F-IT-1', N'零售系统组', N'小组', N'F-IT', NULL, NULL, 10),
(N'F-IT-2', N'数据平台组', N'小组', N'F-IT', NULL, NULL, 20),
(N'F-IT-2-1', N'实时计算与指标组', N'岗位组', N'F-IT-2', NULL, NULL, 10),
(N'F-IT-2-2', N'搜索与推荐组', N'岗位组', N'F-IT-2', NULL, NULL, 20),
(N'F-ATL-1', N'高级珠宝定制组', N'小组', N'F-ATL', NULL, NULL, 10),
(N'F-ATL-2', N'镶嵌与抛光组', N'小组', N'F-ATL', NULL, NULL, 20),
(N'F-ATL-1-1',N'宝石鉴定岗', N'岗位组', N'F-ATL-1', NULL, NULL, 10);
-- 两步走：先全部插入（ParentId 留空），再统一回填父子关系。
-- 原因：单条 INSERT ... SELECT 里 JOIN 目标表，看不到同一语句刚插入的行。
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, StoreCode, SortOrder)
SELECT s.OrgCode, s.OrgName, s.OrgType, NULL, s.CountryCode, s.StoreCode, s.SortOrder
FROM #OrgSeed s
ORDER BY s.Seq;
GO
UPDATE u
SET u.ParentId = p.OrgUnitId
FROM hr.OrgUnit u
JOIN #OrgSeed s ON s.OrgCode = u.OrgCode
LEFT JOIN hr.OrgUnit p ON p.OrgCode = s.ParentCode;
GO
-- ---------------------------------------------------------------------------
-- 1.6 用递归 CTE 维护冗余的 Lvl / Path
-- 这是递归 CTE 最正当的用途之一：一次性回填物化列。
-- 注意三个强制约束：
-- (a) 锚点与递归成员的列类型必须完全一致（这里都显式 CAST）
-- (b) 递归成员里不能用聚合 / TOP / DISTINCT / 外部联接
-- (c) 必须用 UNION ALL，用 UNION 会报 252
-- ---------------------------------------------------------------------------
WITH Tree AS (
SELECT
u.OrgUnitId,
CAST(1 AS TINYINT) AS Lvl,
CAST(N'/' + CAST(u.OrgUnitId AS NVARCHAR(10)) + N'/' AS NVARCHAR(400)) AS Path
FROM hr.OrgUnit u
WHERE u.ParentId IS NULL
UNION ALL
SELECT
c.OrgUnitId,
CAST(t.Lvl + 1 AS TINYINT),
CAST(t.Path + CAST(c.OrgUnitId AS NVARCHAR(10)) + N'/' AS NVARCHAR(400))
FROM hr.OrgUnit c
JOIN Tree t ON c.ParentId = t.OrgUnitId
)
UPDATE u
SET u.Lvl = t.Lvl, u.Path = t.Path
FROM hr.OrgUnit u
JOIN Tree t ON t.OrgUnitId = u.OrgUnitId;
GO
UPDATE u
SET IsLeaf = CASE WHEN EXISTS (SELECT 1 FROM hr.OrgUnit ch WHERE ch.ParentId = u.OrgUnitId) THEN 0 ELSE 1 END
FROM hr.OrgUnit u;
GO
PRINT N'组织架构节点：';
SELECT Lvl, OrgType, NodeCnt = COUNT(*) FROM hr.OrgUnit GROUP BY Lvl, OrgType ORDER BY Lvl, OrgType;
GO
-- ---------------------------------------------------------------------------
-- 1.7 构建闭包表
-- 自反对（Depth=0）先落库，再用递归 CTE 一次展开所有祖孙对。
-- 这就是闭包表的建表过程 —— 本质上和递归 CTE 求子树是同一件事。
-- ---------------------------------------------------------------------------
INSERT INTO hr.OrgUnitClosure (AncestorId, DescendantId, Depth)
SELECT OrgUnitId, OrgUnitId, 0 FROM hr.OrgUnit;
WITH Anc AS (
SELECT c.AncestorId, c.DescendantId, c.Depth
FROM hr.OrgUnitClosure c
WHERE c.Depth = 0
UNION ALL
SELECT a.AncestorId, ch.OrgUnitId, a.Depth + 1
FROM Anc a
JOIN hr.OrgUnit ch ON ch.ParentId = a.DescendantId
)
INSERT INTO hr.OrgUnitClosure (AncestorId, DescendantId, Depth)
SELECT a.AncestorId, a.DescendantId, a.Depth
FROM Anc a
WHERE a.Depth > 0;
GO
SELECT ClosureRows = COUNT(*) FROM hr.OrgUnitClosure;
GO
-- ---------------------------------------------------------------------------
-- 1.8 构建嵌套集
-- 难点：嵌套集的 Lft 不是先序序号，而是「进入计数」。
-- 两者关系可以精确推导出来，不需要模拟遍历：
-- Lft = 2 * 先序序号 - 1 - (层级 - 1)
-- Rgt = Lft + 2 * 子树节点数 - 1
-- 先序序号由「排序键」得到，子树节点数直接查闭包表。
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS #Dfs;
WITH Dfs AS (
SELECT
u.OrgUnitId,
CAST(
RIGHT(REPLICATE('0', 5) + CAST(u.SortOrder AS VARCHAR(5)), 5) + N'-' +
RIGHT(REPLICATE('0', 5) + CAST(u.OrgUnitId AS VARCHAR(5)), 5)
AS NVARCHAR(400)) AS SortKey
FROM hr.OrgUnit u
WHERE u.ParentId IS NULL
UNION ALL
SELECT
c.OrgUnitId,
CAST(d.SortKey + N'/' +
RIGHT(REPLICATE('0', 5) + CAST(c.SortOrder AS VARCHAR(5)), 5) + N'-' +
RIGHT(REPLICATE('0', 5) + CAST(c.OrgUnitId AS VARCHAR(5)), 5)
AS NVARCHAR(400))
FROM hr.OrgUnit c
JOIN Dfs d ON c.ParentId = d.OrgUnitId
)
SELECT OrgUnitId, Pre = ROW_NUMBER() OVER (ORDER BY SortKey)
INTO #Dfs
FROM Dfs;
GO
INSERT INTO hr.OrgUnitNestedSet (OrgUnitId, Lft, Rgt, Lvl)
SELECT
d.OrgUnitId,
Lft = 2 * d.Pre - 1 - (ou.Lvl - 1),
Rgt = 2 * d.Pre - 1 - (ou.Lvl - 1) + 2 * sz.SubtreeSize - 1,
Lvl = ou.Lvl
FROM #Dfs d
JOIN hr.OrgUnit ou ON ou.OrgUnitId = d.OrgUnitId
JOIN (SELECT AncestorId, COUNT(*) AS SubtreeSize FROM hr.OrgUnitClosure GROUP BY AncestorId) sz
ON sz.AncestorId = d.OrgUnitId;
GO
-- 嵌套集不变式自检：后代数必须等于 (Rgt - Lft + 1) / 2 - 1
SELECT NestedSetBadNodes = COUNT(*)
FROM hr.OrgUnitNestedSet ns
JOIN (SELECT AncestorId, DescendantCnt = COUNT(*) - 1 FROM hr.OrgUnitClosure GROUP BY AncestorId) d
ON d.AncestorId = ns.OrgUnitId
WHERE (ns.Rgt - ns.Lft + 1) / 2 - 1 <> d.DescendantCnt;
GO
-- ---------------------------------------------------------------------------
-- 1.9 构建 hierarchyid
-- 同层序号必须先算好落到临时表 —— 递归成员里不能写相关子查询/APPLY
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS #Sib;
SELECT
OrgUnitId, ParentId, Lvl,
SibNo = ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY SortOrder, OrgUnitId)
INTO #Sib
FROM hr.OrgUnit;
GO
WITH Hid AS (
SELECT s.OrgUnitId, CAST('/' AS HIERARCHYID) AS NodePath, s.Lvl
FROM #Sib s
WHERE s.ParentId IS NULL
UNION ALL
SELECT
c.OrgUnitId,
CAST(h.NodePath.ToString() + CAST(c.SibNo AS NVARCHAR(10)) + N'/' AS HIERARCHYID),
c.Lvl
FROM #Sib c
JOIN Hid h ON h.OrgUnitId = c.ParentId
)
INSERT INTO hr.OrgUnitHid (OrgUnitId, NodePath, Lvl)
SELECT OrgUnitId, NodePath, Lvl FROM Hid;
GO
SELECT OrgCode, NodePath = CAST(h.NodePath.ToString() AS NVARCHAR(40)), Lvl = h.Lvl
FROM hr.OrgUnitHid h JOIN hr.OrgUnit u ON u.OrgUnitId = h.OrgUnitId
WHERE u.OrgCode IN (N'GRP', N'R-GCN', N'C-CN', N'Y-SHA', N'S-SHA', N'F-IT-2-1')
ORDER BY h.NodePath;
GO
/* --------------------------------------------------------------------------------
01-A 子树查询：五种写法横向对比
题目：取「中国（大中华区 -> 中国）」这棵子树下的全部组织节点
-------------------------------------------------------------------------------- */
PRINT N'';
PRINT N'----- 01-A 取「中国」子树（五种写法，结果必须一致）-----';
GO
DECLARE @cn INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'C-CN');
DECLARE @cnLft INT = (SELECT Lft FROM hr.OrgUnitNestedSet WHERE OrgUnitId = @cn);
DECLARE @cnRgt INT = (SELECT Rgt FROM hr.OrgUnitNestedSet WHERE OrgUnitId = @cn);
DECLARE @cnPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @cn);
DECLARE @cnHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @cn);
SELECT N'邻接表 + 递归 CTE' AS Method, COUNT(*) AS NodeCnt
FROM (
SELECT u.OrgUnitId FROM hr.OrgUnit u WHERE u.OrgUnitId = @cn
UNION ALL
SELECT c.OrgUnitId FROM hr.OrgUnit c JOIN hr.OrgUnit p ON c.ParentId = p.OrgUnitId
WHERE p.OrgUnitId IN (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgUnitId = @cn)
AND c.OrgUnitId <> @cn
) x;
WITH Sub AS (
SELECT u.OrgUnitId, u.Lvl, u.Path FROM hr.OrgUnit u WHERE u.OrgUnitId = @cn
UNION ALL
SELECT c.OrgUnitId, c.Lvl, c.Path FROM hr.OrgUnit c JOIN Sub s ON c.ParentId = s.OrgUnitId
)
SELECT N'邻接表 + 递归 CTE（完整）' AS Method, COUNT(*) AS NodeCnt, MaxLvl = MAX(Lvl) FROM Sub;
SELECT N'物化路径 + LIKE 前缀' AS Method, COUNT(*) AS NodeCnt
FROM hr.OrgUnit u
WHERE u.Path = @cnPath OR u.Path LIKE @cnPath + N'%';
-- 注意那个 OR：节点自身的路径是 '/1/9/'，不带尾部内容，纯 LIKE '/1/9/%' 会漏掉自己。
-- 这是物化路径最容易踩的边界（下面 01-A-2 专门验证）。
SELECT N'闭包表 JOIN' AS Method, COUNT(*) AS NodeCnt
FROM hr.OrgUnitClosure c WHERE c.AncestorId = @cn;
SELECT N'嵌套集 BETWEEN' AS Method, COUNT(*) AS NodeCnt
FROM hr.OrgUnitNestedSet ns WHERE ns.Lft BETWEEN @cnLft AND @cnRgt;
SELECT N'hierarchyid IsDescendantOf' AS Method, COUNT(*) AS NodeCnt
FROM hr.OrgUnitHid h WHERE h.NodePath.IsDescendantOf(@cnHid) = 1;
GO
-- 01-A-2 物化路径的自包含边界，单独验证
DECLARE @cnPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgCode = N'C-CN');
DECLARE @cn INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'C-CN');
SELECT
PathValue = @cnPath,
PureLike_Count = (SELECT COUNT(*) FROM hr.OrgUnit WHERE Path LIKE @cnPath + N'%'),
LikeOrSelf_Count = (SELECT COUNT(*) FROM hr.OrgUnit WHERE Path = @cnPath OR Path LIKE @cnPath + N'%'),
Closure_Count = (SELECT COUNT(*) FROM hr.OrgUnitClosure WHERE AncestorId = @cn),
Diagnosis = CASE
WHEN (SELECT COUNT(*) FROM hr.OrgUnit WHERE Path LIKE @cnPath + N'%')
= (SELECT COUNT(*) FROM hr.OrgUnitClosure WHERE AncestorId = @cn)
THEN N'一致' ELSE N'纯 LIKE 漏掉了节点自身（少 1 行）' END;
GO
/* --------------------------------------------------------------------------------
01-B 祖先链查询：从最深的门店回溯到集团
-------------------------------------------------------------------------------- */
PRINT N'';
PRINT N'----- 01-B 取「上海南京西路旗舰店」到根的祖先链（五种写法）-----';
GO
DECLARE @leaf INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'S-SHA');
DECLARE @leafPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @leaf);
DECLARE @leafLft INT = (SELECT Lft FROM hr.OrgUnitNestedSet WHERE OrgUnitId = @leaf);
DECLARE @leafRgt INT = (SELECT Rgt FROM hr.OrgUnitNestedSet WHERE OrgUnitId = @leaf);
DECLARE @leafHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @leaf);
PRINT N'[1] 邻接表 + 递归 CTE（自下而上）';
WITH Up AS (
SELECT u.OrgUnitId, u.ParentId, u.OrgName, u.Lvl, CAST(N'' AS NVARCHAR(400)) AS Indent
FROM hr.OrgUnit u WHERE u.OrgUnitId = @leaf
UNION ALL
SELECT p.OrgUnitId, p.ParentId, p.OrgName, p.Lvl, CAST(N'' AS NVARCHAR(400))
FROM hr.OrgUnit p JOIN Up c ON c.ParentId = p.OrgUnitId
)
SELECT ChainOrder = ROW_NUMBER() OVER (ORDER BY Lvl), OrgName, Lvl FROM Up ORDER BY Lvl;
PRINT N'[2] 物化路径：拆字符串 + 逐段查回名字（SQL Server 2022+ 的 STRING_SPLIT 第三参数返回序号）';
SELECT ChainOrder = s.ordinal, u.OrgName, u.Lvl
FROM STRING_SPLIT(SUBSTRING(@leafPath, 2, LEN(@leafPath) - 2), N'/', 1) AS s
JOIN hr.OrgUnit u ON u.OrgUnitId = TRY_CAST(s.value AS INT)
ORDER BY s.ordinal;
PRINT N'[3] 闭包表反向 JOIN';
SELECT c.Depth, u.OrgName, u.Lvl
FROM hr.OrgUnitClosure c JOIN hr.OrgUnit u ON u.OrgUnitId = c.AncestorId
WHERE c.DescendantId = @leaf
ORDER BY c.Depth DESC;
PRINT N'[4] 嵌套集：所有包含我的区间';
SELECT u.OrgName, u.Lvl
FROM hr.OrgUnitNestedSet ns JOIN hr.OrgUnit u ON u.OrgUnitId = ns.OrgUnitId
WHERE ns.Lft <= @leafLft AND ns.Rgt >= @leafRgt
ORDER BY ns.Lft;
PRINT N'[5] hierarchyid GetAncestor 逐层';
SELECT
Lvl = lv.value,
OrgName = (SELECT u.OrgName FROM hr.OrgUnitHid h JOIN hr.OrgUnit u ON u.OrgUnitId = h.OrgUnitId
WHERE h.NodePath = @leafHid.GetAncestor(@leafHid.GetLevel() - lv.value))
FROM GENERATE_SERIES(0, CAST(@leafHid.GetLevel() AS INT)) AS lv
ORDER BY lv.value;
GO
/* --------------------------------------------------------------------------------
01-C / 01-D 写入成本实测
方法：同一操作重复 N 次，包在事务里跑完 ROLLBACK，保证树的初始状态不变，
这样五种方案面对的是同一棵树，数字才可比。
-------------------------------------------------------------------------------- */
PRINT N'';
PRINT N'----- 01-C 新增叶子节点（在「中国香港核心商圈」下新开一家门店，重复 1000 次）-----';
GO
CREATE TABLE #Bench (
Seq INT IDENTITY(1,1),
Op NVARCHAR(24),
Method NVARCHAR(30),
Iterations INT,
TotalUs BIGINT,
PerOpUs DECIMAL(18,2)
);
GO
DECLARE @N INT = 1000;
DECLARE @i INT, @t DATETIME2, @us BIGINT;
DECLARE @parent INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'Y-HK');
DECLARE @parentPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @parent);
DECLARE @parentLvl TINYINT = (SELECT Lvl FROM hr.OrgUnit WHERE OrgUnitId = @parent);
DECLARE @newId INT, @rgt INT;
-- A. 邻接表：一行 INSERT 完事
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, SortOrder, IsLeaf)
VALUES (N'BNC-A-' + CAST(@i AS NVARCHAR(6)), N'压测门店A', N'门店', @parent, 'HK', 999, 1);
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'新增叶子', N'邻接表', @N, @us, @us * 1.0 / @N);
-- B. 邻接表 + 物化路径：INSERT 之后还要回填 Lvl/Path（同一事务内）
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, SortOrder, Lvl, Path, IsLeaf)
VALUES (N'BNC-B-' + CAST(@i AS NVARCHAR(6)), N'压测门店B', N'门店', @parent, 'HK', 999, @parentLvl + 1, N'', 1);
SET @newId = SCOPE_IDENTITY();
UPDATE hr.OrgUnit SET Path = @parentPath + CAST(@newId AS NVARCHAR(10)) + N'/'
WHERE OrgUnitId = @newId;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'新增叶子', N'邻接表+物化路径', @N, @us, @us * 1.0 / @N);
-- C. 闭包表：新增一个节点要写 1 + 深度 行
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, SortOrder, IsLeaf)
VALUES (N'BNC-C-' + CAST(@i AS NVARCHAR(6)), N'压测门店C', N'门店', @parent, 'HK', 999, 1);
SET @newId = SCOPE_IDENTITY();
INSERT INTO hr.OrgUnitClosure (AncestorId, DescendantId, Depth)
SELECT oc.AncestorId, @newId, oc.Depth + 1
FROM hr.OrgUnitClosure oc WHERE oc.DescendantId = @parent
UNION ALL
SELECT @newId, @newId, 0;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'新增叶子', N'闭包表', @N, @us, @us * 1.0 / @N);
-- D. 嵌套集：每插一个节点，右侧所有节点要平移 2 —— 典型 O(N)
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, SortOrder, IsLeaf)
VALUES (N'BNC-D-' + CAST(@i AS NVARCHAR(6)), N'压测门店D', N'门店', @parent, 'HK', 999, 1);
SET @newId = SCOPE_IDENTITY();
SELECT @rgt = Rgt FROM hr.OrgUnitNestedSet WHERE OrgUnitId = @parent;
UPDATE hr.OrgUnitNestedSet SET Rgt = Rgt + 2 WHERE Rgt >= @rgt;
UPDATE hr.OrgUnitNestedSet SET Lft = Lft + 2 WHERE Lft > @rgt;
INSERT INTO hr.OrgUnitNestedSet (OrgUnitId, Lft, Rgt, Lvl)
VALUES (@newId, @rgt, @rgt + 1, @parentLvl + 1);
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'新增叶子', N'嵌套集', @N, @us, @us * 1.0 / @N);
-- E. hierarchyid：拿到父路径 + 同层下一个序号，拼一行
DECLARE @parentHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @parent);
DECLARE @sibMax INT = (SELECT ISNULL(MAX(SibNo), 0) FROM #Sib WHERE ParentId = @parent);
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
INSERT INTO hr.OrgUnit (OrgCode, OrgName, OrgType, ParentId, CountryCode, SortOrder, IsLeaf)
VALUES (N'BNC-E-' + CAST(@i AS NVARCHAR(6)), N'压测门店E', N'门店', @parent, 'HK', 999, 1);
SET @newId = SCOPE_IDENTITY();
INSERT INTO hr.OrgUnitHid (OrgUnitId, NodePath, Lvl)
VALUES (@newId, CAST(@parentHid.ToString() + CAST(@sibMax + @i + 1 AS NVARCHAR(10)) + N'/' AS HIERARCHYID), @parentLvl + 1);
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'新增叶子', N'hierarchyid', @N, @us, @us * 1.0 / @N);
GO
PRINT N'';
PRINT N'----- 01-D 移动子树（把「中国香港核心商圈」连同 2 家门店从中国香港挪到中国，重复 300 次）-----';
GO
DECLARE @N INT = 300;
DECLARE @i INT, @t DATETIME2, @us BIGINT;
DECLARE @fromP INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'C-HK');
DECLARE @toP INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'C-CN');
DECLARE @sub INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'Y-HK');
DECLARE @fromPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @fromP);
DECLARE @subPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @sub);
DECLARE @subLvl TINYINT = (SELECT Lvl FROM hr.OrgUnit WHERE OrgUnitId = @sub);
DECLARE @toPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @toP);
DECLARE @toHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @toP);
DECLARE @fromHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @fromP);
DECLARE @subHid HIERARCHYID = (SELECT NodePath FROM hr.OrgUnitHid WHERE OrgUnitId = @sub);
DECLARE @hidA HIERARCHYID = (SELECT h.NodePath FROM hr.OrgUnitHid h
JOIN hr.OrgUnit u ON u.OrgUnitId = h.OrgUnitId WHERE u.OrgCode = N'Y-HK-A');
DECLARE @newPath NVARCHAR(400), @newLvl TINYINT, @newHid HIERARCHYID;
DECLARE @subSet TABLE (OrgUnitId INT PRIMARY KEY, Depth INT);
INSERT INTO @subSet SELECT DescendantId, Depth FROM hr.OrgUnitClosure WHERE AncestorId = @sub;
-- 子树被挪过去之后，它的 Path 前缀会变成 <目标父节点路径>/<子树ID>/
DECLARE @subIdStr NVARCHAR(10) = CAST(@sub AS NVARCHAR(10));
DECLARE @movedPath NVARCHAR(400) = @toPath + @subIdStr + N'/';
-- A. 邻接表：只更新 ParentId 那一行 —— 最便宜，但冗余列会失真（下面有验证）
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
UPDATE hr.OrgUnit SET ParentId = CASE WHEN @i % 2 = 0 THEN @toP ELSE @fromP END WHERE OrgUnitId = @sub;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'移动子树', N'邻接表（仅ParentId）', @N, @us, @us * 1.0 / @N);
-- B. 物化路径：整棵子树的 Path 前缀必须替换掉
-- ★ 必须来回交替方向：单向 UPDATE 在第一轮之后就没有行可改了，测出来是空转
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
-- ★ 后缀长度 = LEN(Path) - LEN(前缀)，不要 +1：
-- 子树根节点自己的后缀是空串，+1 会多带一个 '/'，拼出 '/1/2/24//'
IF @i % 2 = 0
UPDATE hr.OrgUnit
SET Path = @movedPath + RIGHT(Path, LEN(Path) - LEN(@subPath))
WHERE Path = @subPath OR Path LIKE @subPath + N'%';
ELSE
UPDATE hr.OrgUnit
SET Path = @subPath + RIGHT(Path, LEN(Path) - LEN(@movedPath))
WHERE Path = @movedPath OR Path LIKE @movedPath + N'%';
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'移动子树', N'邻接表+物化路径', @N, @us, @us * 1.0 / @N);
-- C. 闭包表：删掉所有「跨边界」的祖孙对，再按新祖先重新插入
-- 注意：@newAnc 里 @toP 自己那条（Depth 0 + 1 = 1）已经由下面的 SELECT 带出来了，
-- 再补一条 VALUES 会撞主键（实测过，报 2627）。
DECLARE @newAnc TABLE (OrgUnitId INT PRIMARY KEY, Depth INT);
INSERT INTO @newAnc
SELECT c.AncestorId, c.Depth + 1
FROM hr.OrgUnitClosure c
WHERE c.DescendantId = @toP;
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
DELETE oc
FROM hr.OrgUnitClosure oc
JOIN @subSet s ON s.OrgUnitId = oc.DescendantId
WHERE oc.Depth > 0 AND oc.AncestorId NOT IN (SELECT OrgUnitId FROM @subSet);
INSERT INTO hr.OrgUnitClosure (AncestorId, DescendantId, Depth)
SELECT na.OrgUnitId, s.OrgUnitId, na.Depth + s.Depth
FROM @newAnc na CROSS JOIN @subSet s;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'移动子树', N'闭包表（删+重插）', @N, @us, @us * 1.0 / @N);
-- D. hierarchyid：GetReparentedValue 也要逐行改写整棵子树
-- @subSet 已经由闭包表带出「子树全部节点（含自身）」，不要再往里插一遍（会撞主键）
--
-- ★ 两个实测踩出来的坑：
-- 1) GetReparentedValue 只会把子树搬到目标父节点的「第 1 个孩子」槽位。
-- 如果目标已经有第一个孩子，就会撞 UQ_OH_NodePath（报 2601）。
-- 所以这里必须落在一个【空节点】上 —— 就是刚加的「港岛东区（待启用）」。
-- 2) 如果不来回交替方向，第二轮起 oldRoot 已经不是祖先了，
-- CLR 层直接抛 HierarchyIdException 24009（报 6522）。
DECLARE @posUnderA HIERARCHYID = CASE WHEN @hidA IS NULL THEN NULL
ELSE CAST(@hidA.ToString() + N'1/' AS HIERARCHYID) END;
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
IF @i % 2 = 0
UPDATE h SET NodePath = h.NodePath.GetReparentedValue(@subHid, @posUnderA)
FROM hr.OrgUnitHid h JOIN @subSet s ON s.OrgUnitId = h.OrgUnitId;
ELSE
UPDATE h SET NodePath = h.NodePath.GetReparentedValue(@posUnderA, @subHid)
FROM hr.OrgUnitHid h JOIN @subSet s ON s.OrgUnitId = h.OrgUnitId;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'移动子树', N'hierarchyid', @N, @us, @us * 1.0 / @N);
-- E. 嵌套集：工程上普遍用「离线整表重算」，因为增量区间平移极容易写错。
-- 重算需要「先序序号」和「子树大小」—— 前者来自 #Dfs，后者来自闭包表。
-- 注意必须算出真实的 Rgt：写成常量会撞 UQ_ONS_Rgt 唯一索引（实测报 2601）。
BEGIN TRAN;
SET @i = 0; SET @t = SYSDATETIME();
WHILE @i < @N
BEGIN
UPDATE ns
SET ns.Lft = x.Lft, ns.Rgt = x.Rgt
FROM hr.OrgUnitNestedSet ns
JOIN (
SELECT d.OrgUnitId,
Lft = 2 * d.Pre - 1 - (ou.Lvl - 1),
Rgt = 2 * d.Pre - 1 - (ou.Lvl - 1) + 2 * sz.Sz - 1
FROM #Dfs d
JOIN hr.OrgUnit ou ON ou.OrgUnitId = d.OrgUnitId
JOIN (SELECT AncestorId, Sz = COUNT(*) FROM hr.OrgUnitClosure GROUP BY AncestorId) sz
ON sz.AncestorId = d.OrgUnitId
) x ON x.OrgUnitId = ns.OrgUnitId;
SET @i += 1;
END
SET @us = DATEDIFF(MICROSECOND, @t, SYSDATETIME());
ROLLBACK;
INSERT INTO #Bench (Op, Method, Iterations, TotalUs, PerOpUs)
VALUES (N'移动子树', N'嵌套集（整表重算）', @N, @us, @us * 1.0 / @N);
GO
SELECT Op, Method, Iterations, TotalMs = CAST(TotalUs / 1000.0 AS DECIMAL(18,1)),
PerOpUs = CAST(PerOpUs AS DECIMAL(18,1)),
RelativeCost = CAST(PerOpUs / MIN(PerOpUs) OVER (PARTITION BY Op) AS DECIMAL(10,1))
FROM #Bench ORDER BY Op DESC, PerOpUs;
GO
-- 01-D-2 ★ 冗余列失真演示：所有基准测试都 ROLLBACK 了，所以这里显式做一次「只改父指针」，
-- 把邻接表方案的静默数据损坏现出来，看完再回滚。
BEGIN TRAN;
DECLARE @mvSub INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'Y-HK');
DECLARE @mvTo INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'C-CN');
DECLARE @mvSubPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @mvSub);
PRINT N'[执行] UPDATE hr.OrgUnit SET ParentId = 中国 WHERE OrgCode = N''Y-HK''; -- 只动一列';
UPDATE hr.OrgUnit SET ParentId = @mvTo WHERE OrgUnitId = @mvSub;
SELECT
NodeName = o.OrgName,
ParentNow = ISNULL(p.OrgName, N'<无>'),
ActualPath = CAST(o.Path AS NVARCHAR(60)),
ExpectedPrefix = @mvSubPath,
Diagnosis = CASE
WHEN o.OrgCode = N'Y-HK' AND o.Lvl <> 4
THEN N'Lvl 没跟着改 -> 报表按层级过滤会漏掉'
WHEN o.Path NOT LIKE N'%' + CAST(o.ParentId AS NVARCHAR(10)) + N'/%'
THEN N'Path 前缀与新的 ParentId 不一致 -> 静默数据损坏'
ELSE N'一致' END
FROM hr.OrgUnit o
LEFT JOIN hr.OrgUnit p ON p.OrgUnitId = o.ParentId
WHERE o.OrgCode IN (N'Y-HK', N'S-HK-CEN', N'S-HK-TST')
ORDER BY o.Lvl;
PRINT N'结论：邻接表写入最便宜，但所有冗余列（Lvl / Path）都要靠应用层自己维护，漏一次就是静默错误。';
ROLLBACK;
PRINT N'[已回滚] 组织树恢复原状';
GO
GO
/* ================================================================================
02 递归 CTE 基础：HR 组织架构的上下行遍历与层级汇总
================================================================================ */
PRINT N'';
PRINT N'===== 02 递归 CTE 基础：组织架构 =====';
GO
-- 02.1 自顶向下展开整棵树：带层级、缩进、排序路径
PRINT N'----- 02.1 全量组织树（缩进展示，前 30 行）-----';
WITH Tree AS (
SELECT
u.OrgUnitId, u.OrgName, u.OrgType, u.ParentId,
CAST(1 AS INT) AS Lvl,
CAST(RIGHT(REPLICATE('0',5) + CAST(u.SortOrder AS VARCHAR(5)),5) + N'-'
+ RIGHT(REPLICATE('0',5) + CAST(u.OrgUnitId AS VARCHAR(5)),5) AS NVARCHAR(400)) AS SortPath
FROM hr.OrgUnit u
WHERE u.ParentId IS NULL
UNION ALL
SELECT
c.OrgUnitId, c.OrgName, c.OrgType, c.ParentId,
t.Lvl + 1,
CAST(t.SortPath + N'/' + RIGHT(REPLICATE('0',5) + CAST(c.SortOrder AS VARCHAR(5)),5)
+ N'-' + RIGHT(REPLICATE('0',5) + CAST(c.OrgUnitId AS VARCHAR(5)),5) AS NVARCHAR(400))
FROM hr.OrgUnit c
JOIN Tree t ON c.ParentId = t.OrgUnitId
)
SELECT TOP 30
Lvl,
Indent = CAST(REPLICATE(N' ', Lvl - 1) + OrgName AS NVARCHAR(60)),
OrgType,
OrgUnitId
FROM Tree
ORDER BY SortPath
OPTION (MAXRECURSION 100);
GO
-- 02.2 只要某一层 / 只要叶子
PRINT N'----- 02.2 只要第 4 层（城市/部门）与所有叶子 -----';
WITH Tree AS (
SELECT u.OrgUnitId, u.OrgName, u.OrgType, CAST(1 AS INT) AS Lvl
FROM hr.OrgUnit u WHERE u.ParentId IS NULL
UNION ALL
SELECT c.OrgUnitId, c.OrgName, c.OrgType, t.Lvl + 1
FROM hr.OrgUnit c JOIN Tree t ON c.ParentId = t.OrgUnitId
)
SELECT Lvl, OrgType, NodeCnt = COUNT(*) FROM Tree GROUP BY Lvl, OrgType ORDER BY Lvl, OrgType;
GO
-- 02.3 自下而上：从某个门店回溯到集团，并把路径拼成字符串
-- 注意：递归成员里【不能】用 STRING_AGG / 聚合，只能靠「带着字符串往下走」实现
PRINT N'----- 02.3 从 3 家门店回溯到集团，输出祖先链字符串 -----';
DECLARE @leaves TABLE (OrgCode NVARCHAR(20) PRIMARY KEY);
INSERT INTO @leaves VALUES (N'S-SHA'), (N'S-DXB'), (N'F-IT-2-1');
WITH Anchor AS (
SELECT u.OrgUnitId, u.ParentId, u.OrgName, u.Lvl,
CAST(u.OrgName AS NVARCHAR(400)) AS ChainUp, -- 自底向上累积
CAST(u.OrgName AS NVARCHAR(400)) AS ChainDown -- 自顶向下累积（下面反向再拼）
FROM hr.OrgUnit u
JOIN @leaves l ON l.OrgCode = u.OrgCode
UNION ALL
SELECT p.OrgUnitId, p.ParentId, p.OrgName, p.Lvl,
CAST(p.OrgName + N' <- ' + a.ChainUp AS NVARCHAR(400)),
CAST(p.OrgName + N' -> ' + a.ChainDown AS NVARCHAR(400))
FROM hr.OrgUnit p
JOIN Anchor a ON a.ParentId = p.OrgUnitId
)
SELECT OrgName, Lvl, AncestorChain = CAST(ChainUp AS NVARCHAR(120))
FROM Anchor
WHERE Lvl = 1;
GO
-- 02.4 层级汇总
-- 关键认知：「从某个祖先出发递归展开的全部后代」= 闭包表里 AncestorId 等于该祖先的那一片。
-- 所以「递归 CTE 产出 (祖先,后代) 对 + GROUP BY」与「闭包表 JOIN + GROUP BY」必然等价。
-- 反面教材（很多人第一次都这么写）：对单根子树的结果集直接 GROUP BY OrgName 再 COUNT(*)。
-- 那样每个分组只有 1 行，COUNT(*) 恒为 1，配 HAVING COUNT(*) > 1 就会一行都不剩。
-- 正确姿势：递归成员要把「祖先」一路带在结果里，产出成对的 (AncestorId, DescendantId)。
PRINT N'----- 02.4 层级汇总：把门店数/子节点数滚到每个祖先（递归 CTE）-----';
DECLARE @root INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN');
WITH Sub AS ( -- ① 先框出「大中华区」这棵子树的节点集合
SELECT OrgUnitId = u.OrgUnitId
FROM hr.OrgUnit u
WHERE u.OrgUnitId = @root
UNION ALL
SELECT c.OrgUnitId
FROM hr.OrgUnit c JOIN Sub s ON c.ParentId = s.OrgUnitId
),
Pair AS ( -- ② 每个节点先与自己配对，再一路把后代配下来
SELECT AncestorId = s.OrgUnitId, DescendantId = s.OrgUnitId
FROM Sub s
UNION ALL
SELECT p.AncestorId, c.OrgUnitId
FROM Pair p
JOIN hr.OrgUnit c ON c.ParentId = p.DescendantId
)
SELECT
ou.OrgName,
Lvl = ou.Lvl,
SubtreeNodes = COUNT(*),
StoreCount = SUM(CASE WHEN d.OrgType = N'门店' THEN 1 ELSE 0 END),
CityCount = SUM(CASE WHEN d.OrgType = N'城市' THEN 1 ELSE 0 END)
FROM Pair p
JOIN hr.OrgUnit ou ON ou.OrgUnitId = p.AncestorId -- ou = 祖先，用来出名字/层级
JOIN hr.OrgUnit d ON d.OrgUnitId = p.DescendantId -- d = 后代，用来判类型（关键：别用错）
GROUP BY ou.OrgUnitId, ou.OrgName, ou.Lvl
HAVING COUNT(*) > 1
ORDER BY ou.Lvl, SubtreeNodes DESC;
GO
-- 02.5 同一个结果用闭包表写一遍，验证与递归 CTE 完全等价
PRINT N'----- 02.5 等价写法：闭包表（结果应与 02.4 完全一致）-----';
DECLARE @root2 INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN');
SELECT
ou.OrgName,
Lvl = ou.Lvl,
SubtreeNodes = COUNT(*),
StoreCount = SUM(CASE WHEN d.OrgType = N'门店' THEN 1 ELSE 0 END),
CityCount = SUM(CASE WHEN d.OrgType = N'城市' THEN 1 ELSE 0 END)
FROM hr.OrgUnitClosure c
JOIN hr.OrgUnit ou ON ou.OrgUnitId = c.AncestorId
JOIN hr.OrgUnit d ON d.OrgUnitId = c.DescendantId
WHERE c.AncestorId IN (SELECT DescendantId FROM hr.OrgUnitClosure WHERE AncestorId = @root2)
GROUP BY ou.OrgUnitId, ou.OrgName, ou.Lvl
HAVING COUNT(*) > 1
ORDER BY ou.Lvl, SubtreeNodes DESC;
GO
-- 02.4-X 一致性自检：两条路子的 (祖先,后代) 对集合互差必须为空
PRINT N'----- 02.4-X 一致性自检：递归 CTE 与闭包表的双向差集行数（应为 0 / 0）-----';
DECLARE @root3 INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN');
WITH Sub AS (
SELECT OrgUnitId = u.OrgUnitId
FROM hr.OrgUnit u
WHERE u.OrgUnitId = @root3
UNION ALL
SELECT c.OrgUnitId
FROM hr.OrgUnit c JOIN Sub s ON c.ParentId = s.OrgUnitId
),
Pair AS (
SELECT AncestorId = s.OrgUnitId, DescendantId = s.OrgUnitId
FROM Sub s
UNION ALL
SELECT p.AncestorId, c.OrgUnitId
FROM Pair p
JOIN hr.OrgUnit c ON c.ParentId = p.DescendantId
),
Rec AS (
SELECT p.AncestorId, p.DescendantId FROM Pair p
),
Clo AS (
SELECT c.AncestorId, c.DescendantId
FROM hr.OrgUnitClosure c
WHERE c.AncestorId IN (SELECT DescendantId FROM hr.OrgUnitClosure WHERE AncestorId = @root3)
)
SELECT RecOnly = (SELECT COUNT(*) FROM (SELECT * FROM Rec EXCEPT SELECT * FROM Clo) a),
CloOnly = (SELECT COUNT(*) FROM (SELECT * FROM Clo EXCEPT SELECT * FROM Rec) b),
RecRows = (SELECT COUNT(*) FROM Rec),
CloRows = (SELECT COUNT(*) FROM Clo);
GO
-- 02.6 hr.Employee.ManagerId 这条真实的汇报链（36 人，单根）
-- 用「累积排序键」把树按前序遍历铺平：孩子排在父亲后面、同级按 EmployeeId。
-- 这是递归 CTE 最实用的一招 -- 结果集自带稳定顺序，不用再额外排序。
PRINT N'----- 02.6 员工汇报链：全公司层级（缩进 + 完整汇报链）-----';
WITH EmpTree AS (
SELECT e.EmployeeId, e.FullName, e.JobTitle, e.ManagerId,
Lvl = CAST(1 AS INT),
Chain = CAST(e.FullName AS NVARCHAR(400)),
SortPath = CAST(RIGHT(N'00000' + CAST(e.EmployeeId AS VARCHAR(10)), 5) AS VARCHAR(400))
FROM hr.Employee e WHERE e.ManagerId IS NULL
UNION ALL
SELECT e.EmployeeId, e.FullName, e.JobTitle, e.ManagerId,
Lvl = t.Lvl + 1,
Chain = CAST(t.Chain + N' > ' + e.FullName AS NVARCHAR(400)),
SortPath = CAST(t.SortPath + N'.' + RIGHT(N'00000' + CAST(e.EmployeeId AS VARCHAR(10)), 5) AS VARCHAR(400))
FROM hr.Employee e JOIN EmpTree t ON e.ManagerId = t.EmployeeId
)
SELECT
e.Lvl,
Indent = CAST(REPLICATE(N' ', e.Lvl - 1) + e.FullName AS NVARCHAR(60)),
e.JobTitle,
Chain = CAST(e.Chain AS NVARCHAR(160))
FROM EmpTree e
ORDER BY e.SortPath;
GO
/* ================================================================================
03 ERP 商品品类树
================================================================================ */
PRINT N'';
PRINT N'===== 03 ERP 商品品类树 =====';
GO
DROP TABLE IF EXISTS erp.ProductCategoryClosure;
DROP TABLE IF EXISTS erp.ProductCategory;
GO
CREATE TABLE erp.ProductCategory (
CategoryId INT IDENTITY(1,1) NOT NULL,
CategoryCode VARCHAR(20) NOT NULL,
CategoryName NVARCHAR(40) NOT NULL,
ParentCategoryId INT NULL,
Lvl TINYINT NULL,
Path NVARCHAR(200) NULL,
FullPathName NVARCHAR(300) NULL, -- 冗余：/珠宝首饰/戒指/钻石戒指/
IsLeaf BIT NOT NULL CONSTRAINT DF_PC_Leaf DEFAULT (0),
SortOrder INT NOT NULL CONSTRAINT DF_PC_Sort DEFAULT (10),
CONSTRAINT PK_ProductCategory PRIMARY KEY CLUSTERED (CategoryId),
CONSTRAINT UQ_ProductCategory_Code UNIQUE (CategoryCode),
CONSTRAINT UQ_ProductCategory_Name UNIQUE (CategoryName),
CONSTRAINT FK_ProductCategory_Parent FOREIGN KEY (ParentCategoryId) REFERENCES erp.ProductCategory(CategoryId)
);
GO
CREATE INDEX IX_ProductCategory_Parent ON erp.ProductCategory (ParentCategoryId);
CREATE INDEX IX_ProductCategory_Path ON erp.ProductCategory (Path);
GO
INSERT INTO erp.ProductCategory (CategoryCode, CategoryName, ParentCategoryId, SortOrder) VALUES
('ALL', N'全部商品', NULL, 10),
('JW', N'珠宝首饰', NULL, 10),
('WCH', N'腕表', NULL, 20),
('OTH', N'其他', NULL, 30),
('RG', N'戒指', NULL, 10),
('NK', N'项链', NULL, 20),
('BR', N'手镯', NULL, 30),
('ER', N'耳饰', NULL, 40),
('PD', N'吊坠', NULL, 50),
('RG-D', N'钻石戒指', NULL, 10),
('RG-G', N'宝石戒指', NULL, 20),
('RG-P', N'素金戒指', NULL, 30),
('NK-D', N'钻石项链', NULL, 10),
('NK-P', N'珍珠项链', NULL, 20),
('NK-G', N'黄金项链', NULL, 30),
('BR-H', N'硬足金手镯', NULL, 10),
('BR-S', N'软链手镯', NULL, 20),
('ER-ST',N'耳钉', NULL, 10),
('ER-HP',N'耳环', NULL, 20),
('PD-D', N'钻石吊坠', NULL, 10),
('PD-J', N'翡翠吊坠', NULL, 20),
('WCH-M',N'机械腕表', NULL, 10),
('WCH-S',N'智能穿戴', NULL, 20),
('OTH-P',N'包装与配件', NULL, 10),
('OTH-S',N'服务与工费', NULL, 20),
('RG-D-S',N'单钻戒指', NULL, 10),
('RG-D-H',N'群镶戒指', NULL, 20),
('RG-D-P',N'排镶戒指', NULL, 30),
('RG-G-R',N'红宝石戒指',NULL, 10),
('RG-G-B',N'蓝宝石戒指',NULL, 20),
('RG-G-E',N'祖母绿戒指',NULL, 30),
('NK-P-S',N'海水珍珠', NULL, 10),
('NK-P-F',N'淡水珍珠', NULL, 20);
GO
-- 用代码回填父子关系（代码比 ID 可读得多，也天然避免 ID 依赖）
UPDATE c SET c.ParentCategoryId = p.CategoryId
FROM erp.ProductCategory c
JOIN (VALUES
('JW','ALL'), ('WCH','ALL'), ('OTH','ALL'),
('RG','JW'), ('NK','JW'), ('BR','JW'), ('ER','JW'), ('PD','JW'),
('RG-D','RG'), ('RG-G','RG'), ('RG-P','RG'),
('NK-D','NK'), ('NK-P','NK'), ('NK-G','NK'),
('BR-H','BR'), ('BR-S','BR'),
('ER-ST','ER'), ('ER-HP','ER'),
('PD-D','PD'), ('PD-J','PD'),
('WCH-M','WCH'), ('WCH-S','WCH'),
('OTH-P','OTH'), ('OTH-S','OTH'),
('RG-D-S','RG-D'), ('RG-D-H','RG-D'), ('RG-D-P','RG-D'),
('RG-G-R','RG-G'), ('RG-G-B','RG-G'), ('RG-G-E','RG-G'),
('NK-P-S','NK-P'), ('NK-P-F','NK-P')
) m(Child, Parent) ON m.Child = c.CategoryCode
JOIN erp.ProductCategory p ON p.CategoryCode = m.Parent;
GO
-- 递归维护 Lvl / Path / FullPathName
WITH CatTree AS (
SELECT
c.CategoryId,
CAST(1 AS TINYINT) AS Lvl,
CAST(N'/' + CAST(c.CategoryId AS NVARCHAR(10)) + N'/' AS NVARCHAR(200)) AS Path,
CAST(N'/' + c.CategoryName + N'/' AS NVARCHAR(300)) AS FullPathName
FROM erp.ProductCategory c
WHERE c.ParentCategoryId IS NULL
UNION ALL
SELECT
c.CategoryId,
CAST(t.Lvl + 1 AS TINYINT),
CAST(t.Path + CAST(c.CategoryId AS NVARCHAR(10)) + N'/' AS NVARCHAR(200)),
CAST(t.FullPathName + c.CategoryName + N'/' AS NVARCHAR(300))
FROM erp.ProductCategory c
JOIN CatTree t ON c.ParentCategoryId = t.CategoryId
)
UPDATE c
SET c.Lvl = t.Lvl, c.Path = t.Path, c.FullPathName = t.FullPathName
FROM erp.ProductCategory c
JOIN CatTree t ON t.CategoryId = c.CategoryId;
GO
UPDATE c
SET IsLeaf = CASE WHEN EXISTS (SELECT 1 FROM erp.ProductCategory ch WHERE ch.ParentCategoryId = c.CategoryId) THEN 0 ELSE 1 END
FROM erp.ProductCategory c;
GO
SELECT CategoryId, CategoryCode, CategoryName, Lvl, IsLeaf, FullPathName
FROM erp.ProductCategory ORDER BY Path;
GO
-- 把既有的 erp.Product.Category（扁平文本）挂到品类树上
IF COL_LENGTH(N'erp.Product', N'CategoryId') IS NULL
ALTER TABLE erp.Product ADD CategoryId INT NULL;
GO
UPDATE p SET p.CategoryId = c.CategoryId
FROM erp.Product p JOIN erp.ProductCategory c ON c.CategoryName = p.Category
WHERE p.CategoryId IS NULL;
GO
SELECT MappedProducts = COUNT(*) FROM erp.Product WHERE CategoryId IS NOT NULL;
GO
-- 品类闭包表（33 个节点，递归展开即可）
CREATE TABLE erp.ProductCategoryClosure (
AncestorId INT NOT NULL,
DescendantId INT NOT NULL,
Depth INT NOT NULL,
CONSTRAINT PK_ProductCategoryClosure PRIMARY KEY CLUSTERED (AncestorId, DescendantId)
);
GO
INSERT INTO erp.ProductCategoryClosure (AncestorId, DescendantId, Depth)
SELECT CategoryId, CategoryId, 0 FROM erp.ProductCategory;
GO
WITH C AS (
SELECT c.AncestorId, c.DescendantId, c.Depth
FROM erp.ProductCategoryClosure c WHERE c.Depth = 0
UNION ALL
SELECT a.AncestorId, ch.CategoryId, a.Depth + 1
FROM C a JOIN erp.ProductCategory ch ON ch.ParentCategoryId = a.DescendantId
)
INSERT INTO erp.ProductCategoryClosure (AncestorId, DescendantId, Depth)
SELECT AncestorId, DescendantId, Depth FROM C WHERE Depth > 0;
GO
SELECT CategoryClosureRows = COUNT(*) FROM erp.ProductCategoryClosure;
GO
-- 03.1 递归取「戒指」下所有叶子品类，再 JOIN 出全部 SKU
PRINT N'----- 03.1 递归求「戒指」整棵子树，再把 SKU 汇总回来 -----';
-- 商品实际挂在树的中间层，所以口径必须是「本节点 + 全部后代节点」；
-- 只按叶子 JOIN 会得到 0 行 —— 这是层级聚合最常见的口径错误。
DECLARE @rg INT = (SELECT CategoryId FROM erp.ProductCategory WHERE CategoryCode = 'RG');
WITH SubCat AS (
SELECT c.CategoryId, c.CategoryName, c.Lvl, c.IsLeaf
FROM erp.ProductCategory c WHERE c.CategoryId = @rg
UNION ALL
SELECT c.CategoryId, c.CategoryName, c.Lvl, c.IsLeaf
FROM erp.ProductCategory c JOIN SubCat s ON c.ParentCategoryId = s.CategoryId
)
SELECT CategoryName = s.CategoryName, CatLvl = s.Lvl, IsLeaf = s.IsLeaf,
SkuCnt = COUNT(p.ProductId),
AvgPrice = CAST(AVG(p.UnitPrice) AS DECIMAL(18,2))
FROM SubCat s
JOIN erp.Product p ON p.CategoryId = s.CategoryId
GROUP BY s.CategoryName, s.Lvl, s.IsLeaf
ORDER BY s.Lvl, s.CategoryName;
GO
PRINT N'----- 03.1-B 递归到最底层：叶子品类数量（采购/补货按这个口径）-----';
DECLARE @rg2 INT = (SELECT CategoryId FROM erp.ProductCategory WHERE CategoryCode = 'RG');
WITH SubCat2 AS (
SELECT c.CategoryId, c.IsLeaf
FROM erp.ProductCategory c WHERE c.CategoryId = @rg2
UNION ALL
SELECT c.CategoryId, c.IsLeaf
FROM erp.ProductCategory c JOIN SubCat2 s ON c.ParentCategoryId = s.CategoryId
)
SELECT RingLeafCategories = COUNT(*) FROM SubCat2 WHERE IsLeaf = 1;
GO
-- 03.2 自底向上 rollup：把品类销售额累加到所有祖先
-- 「冗余列失真」是层级聚合最经典的两个 bug：漏算与重复计算。
-- 用闭包表 JOIN 一次性解决，且天然可并行（下面 07 节会实测计划差异）。
PRINT N'----- 03.2 品类销售额自底向上 rollup（用已有订单行）-----';
SELECT
AncestorName = a.CategoryName,
a.Lvl,
LeafAmount = CAST(SUM(l.LineAmount) AS DECIMAL(18,2)),
LeafLines = COUNT(*)
FROM erp.SalesOrderLine l
JOIN erp.Product p ON p.ProductId = l.ProductId
JOIN erp.ProductCategoryClosure c ON c.DescendantId = p.CategoryId
JOIN erp.ProductCategory a ON a.CategoryId = c.AncestorId
GROUP BY a.CategoryName, a.Lvl, a.Path
HAVING a.Lvl <= 3
ORDER BY a.Path;
GO
/* ================================================================================
04 ERP BOM 物料清单
---------------------------------------------------------------------------
珠宝 BOM 的特点：
· 层数深（成品 -> 半成品 -> 镶嵌件 -> 料件 -> 镶爪），但每层扇出小
· 有共用件（同一颗主石可能出现在多个成品里），所以本质是 DAG
· 用量要沿路径连乘，且要乘损耗率 —— 这是文档型/图库做不好的地方
================================================================================ */
PRINT N'';
PRINT N'===== 04 ERP BOM 物料清单 =====';
GO
DROP TABLE IF EXISTS erp.BOMLine;
DROP TABLE IF EXISTS erp.Item;
GO
CREATE TABLE erp.Item (
ItemId INT IDENTITY(1,1) NOT NULL,
ItemCode VARCHAR(16) NOT NULL,
ItemName NVARCHAR(60) NOT NULL,
ItemType NVARCHAR(12) NOT NULL, -- 成品/半成品/原料/工艺/包装
UOM VARCHAR(8) NOT NULL,
StdCostUsd DECIMAL(18,4) NOT NULL,
IsPurchased BIT NOT NULL,
LeadTimeDays INT NOT NULL CONSTRAINT DF_Item_LT DEFAULT (7),
CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemId),
CONSTRAINT UQ_Item_Code UNIQUE (ItemCode)
);
GO
INSERT INTO erp.Item (ItemCode, ItemName, ItemType, UOM, StdCostUsd, IsPurchased, LeadTimeDays) VALUES
-- 成品
('FG-R001', N'18K金 1ct 单钻戒指 R001', N'成品', 'PCS', 9850.0000, 0, 0),
('FG-R002', N'PT950 群镶戒指 R002', N'成品', 'PCS', 6480.0000, 0, 0),
('FG-N001', N'18K金 钻石项链 N001', N'成品', 'PCS', 7230.0000, 0, 0),
-- 半成品
('SF-SET01', N'六爪镶嵌托架总成', N'半成品', 'PCS', 1820.0000, 0, 0),
('SF-SET02', N'群镶微镶托架总成', N'半成品', 'PCS', 960.0000, 0, 0),
('SF-SHANK', N'18K 白金戒圈（素圈）', N'半成品', 'PCS', 2150.0000, 0, 0),
('SF-CLAW', N'镶爪组件（单爪）', N'半成品', 'PCS', 38.0000, 0, 0),
('SF-HOOK', N'项链链扣组件', N'半成品', 'PCS', 420.0000, 0, 0),
-- 原料
('RM-AU18K', N'18K 白金料', N'原料', 'G', 46.5000, 1, 21),
('RM-PT950', N'PT950 铂金料', N'原料', 'G', 31.8000, 1, 25),
('RM-DIA1C', N'圆形明亮式钻石 1.00ct D/VVS1',N'原料', 'PCS', 4200.0000, 1, 45),
('RM-DIA005',N'圆钻 0.05ct F/VS2', N'原料', 'PCS', 118.0000, 1, 45),
('RM-DIA002',N'圆钻 0.02ct F/VS2', N'原料', 'PCS', 42.0000, 1, 45),
('RM-CLAW-M',N'镶爪用 18K 金线', N'原料', 'G', 48.0000, 1, 21),
('RM-CHAIN', N'18K 金项链链身', N'原料', 'CM', 96.0000, 1, 30),
-- 工艺
('PR-PLATE', N'镀铑工艺', N'工艺', 'TIMES', 35.0000, 1, 3),
('PR-POLISH',N'抛光工艺', N'工艺', 'TIMES', 22.0000, 1, 2),
('PR-SET', N'手工镶嵌工费', N'工艺', 'TIMES',280.0000, 1, 5),
-- 包装
('PK-BOX', N'高级珠宝绒面礼盒', N'包装', 'PCS', 68.0000, 1, 10),
('PK-CERT', N'GIA 证书工本费', N'包装', 'PCS', 150.0000, 1, 20);
GO
CREATE TABLE erp.BOMLine (
BomLineId INT IDENTITY(1,1) NOT NULL,
ParentItemId INT NOT NULL,
ChildItemId INT NOT NULL,
QtyPer DECIMAL(18,6) NOT NULL, -- 单位用量
ScrapPct DECIMAL(9,4) NOT NULL CONSTRAINT DF_BOM_Scrap DEFAULT (0),
LineSeq INT NOT NULL CONSTRAINT DF_BOM_Line DEFAULT (10),
EffFrom DATE NOT NULL CONSTRAINT DF_BOM_Eff DEFAULT ('2025-01-01'),
EffTo DATE NULL,
CONSTRAINT PK_BOMLine PRIMARY KEY CLUSTERED (BomLineId),
CONSTRAINT FK_BOM_Parent FOREIGN KEY (ParentItemId) REFERENCES erp.Item(ItemId),
CONSTRAINT FK_BOM_Child FOREIGN KEY (ChildItemId) REFERENCES erp.Item(ItemId),
CONSTRAINT UQ_BOM_Edge UNIQUE (ParentItemId, ChildItemId, EffFrom)
);
GO
CREATE INDEX IX_BOM_Child ON erp.BOMLine (ChildItemId) INCLUDE (ParentItemId);
CREATE INDEX IX_BOM_Parent ON erp.BOMLine (ParentItemId, LineSeq);
GO
-- 用物料代码定义 BOM 结构（可读性远比 ID 好）
DROP TABLE IF EXISTS #BomSeed;
CREATE TABLE #BomSeed (Parent VARCHAR(16), Child VARCHAR(16), QtyPer DECIMAL(18,6), Scrap DECIMAL(9,4), LineSeq INT);
INSERT INTO #BomSeed (Parent, Child, QtyPer, Scrap, LineSeq) VALUES
-- 成品 R001：戒圈 + 镶嵌托架 + 主石 + 副石 + 工艺 + 包装
('FG-R001','SF-SHANK', 1.000000, 0.0000, 10),
('FG-R001','SF-SET01', 1.000000, 0.0000, 20),
('FG-R001','RM-DIA1C', 1.000000, 0.0200, 30),
('FG-R001','RM-DIA005',12.000000, 0.0500, 40),
('FG-R001','PR-SET', 1.000000, 0.0000, 50),
('FG-R001','PK-BOX', 1.000000, 0.0000, 60),
('FG-R001','PK-CERT', 1.000000, 0.0000, 70),
-- 戒圈（半成品）由金料 + 抛光构成
('SF-SHANK','RM-AU18K', 3.850000, 0.0800, 10),
('SF-SHANK','PR-POLISH',1.000000, 0.0000, 20),
('SF-SHANK','PR-PLATE', 1.000000, 0.0000, 30),
-- 六爪托架：4 个镶爪组件 + 金料 + 镀铑
('SF-SET01','SF-CLAW', 4.000000, 0.0300, 10),
('SF-SET01','RM-AU18K', 0.620000, 0.0800, 20),
('SF-SET01','PR-PLATE', 1.000000, 0.0000, 30),
-- 镶爪组件由金线加工而来
('SF-CLAW','RM-CLAW-M', 0.045000, 0.1200, 10),
('SF-CLAW','PR-POLISH', 1.000000, 0.0000, 20),
-- 成品 R002 复用 SF-SET01（共用件 -> 这是 DAG 的证据）
('FG-R002','SF-SHANK', 1.000000, 0.0000, 10),
('FG-R002','SF-SET01', 1.000000, 0.0000, 20),
('FG-R002','RM-DIA002',24.000000, 0.0600, 30),
('FG-R002','PR-SET', 1.000000, 0.0000, 40),
('FG-R002','PK-BOX', 1.000000, 0.0000, 50),
-- 成品 N001
('FG-N001','RM-CHAIN', 45.000000, 0.0200, 10),
('FG-N001','SF-HOOK', 1.000000, 0.0000, 20),
('FG-N001','RM-DIA005', 5.000000, 0.0500, 30),
('FG-N001','PR-POLISH', 1.000000, 0.0000, 40),
('FG-N001','PK-BOX', 1.000000, 0.0000, 50),
-- 链扣组件：由金料 + 抛光构成
('SF-HOOK','RM-AU18K', 1.250000, 0.0500, 10),
('SF-HOOK','PR-POLISH', 1.000000, 0.0000, 20),
-- 群镶托架（与六爪托架共用镶爪组件 —— 共用件）
('SF-SET02','SF-CLAW', 6.000000, 0.0300, 10),
('SF-SET02','RM-AU18K', 0.480000, 0.0800, 20),
('SF-SET02','PR-PLATE', 1.000000, 0.0000, 30);
GO
INSERT INTO erp.BOMLine (ParentItemId, ChildItemId, QtyPer, ScrapPct, LineSeq)
SELECT p.ItemId, c.ItemId, s.QtyPer, s.Scrap, s.LineSeq
FROM #BomSeed s
JOIN erp.Item p ON p.ItemCode = s.Parent
JOIN erp.Item c ON c.ItemCode = s.Child;
GO
SELECT BomEdges = COUNT(*) FROM erp.BOMLine;
GO
-- 04.1 单层展开：只看成品的第一层构成
PRINT N'----- 04.1 单层展开：18K金 1ct 单钻戒指 R001 的直接构成 -----';
DECLARE @fg INT = (SELECT ItemId FROM erp.Item WHERE ItemCode = 'FG-R001');
SELECT
b.LineSeq,
Child = c.ItemName,
ChildType = c.ItemType,
b.QtyPer,
ScrapPct = b.ScrapPct,
EffQty = CAST(b.QtyPer * (1 + b.ScrapPct / 100.0) AS DECIMAL(18,6)),
UnitCost = c.StdCostUsd,
LineCost = CAST(b.QtyPer * (1 + b.ScrapPct / 100.0) * c.StdCostUsd AS DECIMAL(18,2))
FROM erp.BOMLine b
JOIN erp.Item c ON c.ItemId = b.ChildItemId
WHERE b.ParentItemId = @fg
ORDER BY b.LineSeq;
GO
-- 04.2 多层展开 + 用量爆炸
-- CumQty 沿路径连乘，并逐层乘上 (1 + 损耗率)
-- 三个必须注意的写法：
-- · 累乘列的类型在锚点与递归成员间必须完全一致（都 CAST 成 DECIMAL(28,6)）
-- · 排序键要能表达「同层按 LineSeq」的顺序 -> 用补零字符串拼接
-- · 递归成员里不能写 GROUP BY / 聚合，所以只能「边下推边算」
PRINT N'----- 04.2 多层 BOM 展开（用量爆炸）-----';
DECLARE @fg2 INT = (SELECT ItemId FROM erp.Item WHERE ItemCode = 'FG-R001');
WITH Expl AS (
SELECT
RootId = i.ItemId,
ItemId = i.ItemId,
ParentId = CAST(NULL AS INT),
Lvl = CAST(0 AS INT),
CumQty = CAST(1.000000 AS DECIMAL(28,6)),
SortPath = CAST(N'-' + RIGHT(REPLICATE('0',6) + CAST(i.ItemId AS VARCHAR(6)),6) AS NVARCHAR(400))
FROM erp.Item i
WHERE i.ItemId = @fg2
UNION ALL
SELECT
e.RootId,
b.ChildItemId,
b.ParentItemId,
e.Lvl + 1,
CAST(e.CumQty * b.QtyPer * (1 + b.ScrapPct / 100.0) AS DECIMAL(28,6)),
CAST(e.SortPath + N'/' + RIGHT(REPLICATE('0',4) + CAST(b.LineSeq AS VARCHAR(4)),4)
+ N'-' + RIGHT(REPLICATE('0',6) + CAST(b.ChildItemId AS VARCHAR(6)),6) AS NVARCHAR(400))
FROM Expl e
JOIN erp.BOMLine b ON b.ParentItemId = e.ItemId
)
SELECT
Lvl,
Indent = CAST(REPLICATE(N' ', Lvl) + it.ItemName AS NVARCHAR(60)),
ItemType = it.ItemType,
UOM = it.UOM,
CumQty,
CumCost = CAST(CumQty * it.StdCostUsd AS DECIMAL(18,4)),
Purchased = CASE WHEN it.IsPurchased = 1 THEN N'采购' ELSE N'自制' END
FROM Expl e
JOIN erp.Item it ON it.ItemId = e.ItemId
ORDER BY e.SortPath
OPTION (MAXRECURSION 32);
GO
-- 04.3 成本卷积：只取采购件（叶子），累加出成品的理论料本
-- 然后和 Item.StdCostUsd 比一比，差多少就是工费与损耗
PRINT N'----- 04.3 成本卷积：叶子采购件累加 vs 成品标准成本 -----';
WITH Expl AS (
SELECT
RootId = i.ItemId, ItemId = i.ItemId,
Lvl = CAST(0 AS INT),
CumQty = CAST(1.000000 AS DECIMAL(28,6))
FROM erp.Item i
WHERE i.ItemType = N'成品'
UNION ALL
SELECT
e.RootId, b.ChildItemId, e.Lvl + 1,
CAST(e.CumQty * b.QtyPer * (1 + b.ScrapPct / 100.0) AS DECIMAL(28,6))
FROM Expl e
JOIN erp.BOMLine b ON b.ParentItemId = e.ItemId
),
Rolled AS (
SELECT
e.RootId,
MaterialCost = CAST(SUM(e.CumQty * it.StdCostUsd) AS DECIMAL(18,4)),
LeafKinds = COUNT(DISTINCT e.ItemId),
MaxDepth = MAX(e.Lvl)
FROM Expl e
JOIN erp.Item it ON it.ItemId = e.ItemId
WHERE it.IsPurchased = 1
GROUP BY e.RootId
)
SELECT
Finished = f.ItemName,
StdCost = f.StdCostUsd,
MaterialCost = r.MaterialCost,
LaborAndScrap = CAST(f.StdCostUsd - r.MaterialCost AS DECIMAL(18,4)),
MaterialShare = CAST(100.0 * r.MaterialCost / f.StdCostUsd AS DECIMAL(10,2)),
LeafKinds = r.LeafKinds,
BomDepth = r.MaxDepth
FROM Rolled r
JOIN erp.Item f ON f.ItemId = r.RootId
ORDER BY f.ItemName;
GO
-- 04.4 where-used 反查：一种原材料到底被哪些成品用到（自底向上）
-- 反查和展开是同一个递归的两个方向：展开是 ChildItemId 往下走，
-- 反查是 ChildItemId 作为入口、ParentItemId 往上走。WHERE 条件一换，方向就反了。
-- 选 18K 白金料 RM-AU18K：它被 4 个半成品直接消耗，最终流入 3 个成品。
PRINT N'----- 04.4 where-used：18K 白金料 RM-AU18K 被哪些上层成品使用 -----';
DECLARE @mat INT = (SELECT ItemId FROM erp.Item WHERE ItemCode = 'RM-AU18K');
WITH Used AS (
SELECT ItemId = b.ParentItemId,
Lvl = CAST(1 AS INT),
SortPath = CAST(RIGHT(REPLICATE('0', 6) + CAST(b.ParentItemId AS VARCHAR(6)), 6) AS NVARCHAR(400))
FROM erp.BOMLine b
WHERE b.ChildItemId = @mat
UNION ALL
SELECT pb.ParentItemId,
u.Lvl + 1,
CAST(u.SortPath + N'/' + RIGHT(REPLICATE('0', 6) + CAST(pb.ParentItemId AS VARCHAR(6)), 6) AS NVARCHAR(400))
FROM Used u
JOIN erp.BOMLine pb ON pb.ChildItemId = u.ItemId
)
SELECT
u.Lvl,
Indent = CAST(REPLICATE(N' ', u.Lvl - 1) + it.ItemName AS NVARCHAR(60)),
it.ItemType,
it.ItemCode
FROM Used u
JOIN erp.Item it ON it.ItemId = u.ItemId
ORDER BY u.SortPath
OPTION (MAXRECURSION 32);
GO
-- 04.4-B 反查的聚合口径：一个物料向下能影响多少成品（供应商断货时的爆炸半径）
PRINT N'----- 04.4-B 断货爆炸半径：RM-AU18K 影响的半成品数 / 成品数 -----';
DECLARE @mat2 INT = (SELECT ItemId FROM erp.Item WHERE ItemCode = 'RM-AU18K');
WITH Used AS (
SELECT ItemId = b.ParentItemId
FROM erp.BOMLine b
WHERE b.ChildItemId = @mat2
UNION ALL
SELECT pb.ParentItemId
FROM Used u
JOIN erp.BOMLine pb ON pb.ChildItemId = u.ItemId
)
SELECT DirectParents = (SELECT COUNT(DISTINCT ParentItemId) FROM erp.BOMLine WHERE ChildItemId = @mat2),
AffectedNodes = (SELECT COUNT(DISTINCT ItemId) FROM Used),
FinishedGoods = (SELECT COUNT(DISTINCT u.ItemId)
FROM Used u JOIN erp.Item it ON it.ItemId = u.ItemId
WHERE it.ItemType = N'成品');
GO
-- 04.5 采购需求：只取最底层采购件，直接换算成采购单行
PRINT N'----- 04.5 生产 100 枚 R001 的采购需求（只取叶子）-----';
DECLARE @qty DECIMAL(18,6) = 100;
WITH Expl AS (
SELECT i.ItemId AS RootId, i.ItemId, Lvl = CAST(0 AS INT), CumQty = CAST(1.000000 AS DECIMAL(28,6))
FROM erp.Item i WHERE i.ItemCode = 'FG-R001'
UNION ALL
SELECT e.RootId, b.ChildItemId, e.Lvl + 1,
CAST(e.CumQty * b.QtyPer * (1 + b.ScrapPct / 100.0) AS DECIMAL(28,6))
FROM Expl e JOIN erp.BOMLine b ON b.ParentItemId = e.ItemId
)
SELECT
ItemCode = it.ItemCode,
ItemName = it.ItemName,
UOM = it.UOM,
RequireQty = CAST(SUM(e.CumQty) * @qty AS DECIMAL(18,4)),
-- ★ it.StdCostUsd 必须放进 SUM 里：放在 SUM 外面又不进 GROUP BY 会报 8120
Amount = CAST(SUM(e.CumQty * it.StdCostUsd) * @qty AS DECIMAL(18,2)),
MaxLeadTime = MAX(it.LeadTimeDays)
FROM Expl e
JOIN erp.Item it ON it.ItemId = e.ItemId
WHERE it.IsPurchased = 1
GROUP BY it.ItemCode, it.ItemName, it.UOM
ORDER BY Amount DESC
OPTION (MAXRECURSION 32);
GO
-- 04.6 BOM 环路：真实 BOM 修剪后会出现「A 用 B、B 用 A」的脏数据。
-- 无防护的递归会一直转下去，直到撞上 MAXRECURSION 才报 530 ——
-- 在千万并发的系统里，这意味着一个坏数据能把连接池打满。
PRINT N'----- 04.6 BOM 环路检测 -----';
DROP TABLE IF EXISTS #CycBom;
CREATE TABLE #CycBom (ParentItemId INT, ChildItemId INT, Note NVARCHAR(40));
INSERT INTO #CycBom (ParentItemId, ChildItemId, Note) VALUES
(1, 2, N'正常'), (2, 3, N'正常'), (3, 4, N'正常'),
(4, 2, N'环：4 回指 2'), (3, 5, N'正常'), (5, 6, N'正常');
GO
PRINT N'[A] 无防护的递归（必然撞 MAXRECURSION）';
BEGIN TRY
WITH E AS (
SELECT ParentItemId, ChildItemId, Lvl = CAST(1 AS INT)
FROM #CycBom WHERE ParentItemId = 1
UNION ALL
SELECT b.ParentItemId, b.ChildItemId, e.Lvl + 1
FROM E e JOIN #CycBom b ON b.ParentItemId = e.ChildItemId
)
SELECT CycRows = COUNT(*) FROM E OPTION (MAXRECURSION 200);
END TRY
BEGIN CATCH
SELECT UnprotectedResult = N'被 MAXRECURSION 中止', ErrNo = ERROR_NUMBER(), ErrMsg = LEFT(ERROR_MESSAGE(), 200);
END CATCH
GO
PRINT N'[B] 带路径防护的递归（把祖先链带在身上，命中过就停）';
WITH E AS (
SELECT
ParentItemId, ChildItemId,
Lvl = CAST(1 AS INT),
Chain = CAST(N'>' + CAST(ParentItemId AS NVARCHAR(10)) + N'>' + CAST(ChildItemId AS NVARCHAR(10)) + N'>' AS NVARCHAR(400))
FROM #CycBom WHERE ParentItemId = 1
UNION ALL
SELECT
b.ParentItemId, b.ChildItemId,
e.Lvl + 1,
CAST(e.Chain + CAST(b.ChildItemId AS NVARCHAR(10)) + N'>' AS NVARCHAR(400))
FROM E e
JOIN #CycBom b ON b.ParentItemId = e.ChildItemId
WHERE CHARINDEX(N'>' + CAST(b.ChildItemId AS NVARCHAR(10)) + N'>', e.Chain) = 0
)
SELECT
Lvl,
Chain = CAST(Chain AS NVARCHAR(60)),
Edge = CAST(ParentItemId AS NVARCHAR(10)) + N' -> ' + CAST(ChildItemId AS NVARCHAR(10))
FROM E
ORDER BY Chain
OPTION (MAXRECURSION 64);
GO
PRINT N'[C] 被防护截断的环边（这些才是要修的数据）';
WITH E AS (
SELECT ParentItemId, ChildItemId, Chain = CAST(N'>' + CAST(ParentItemId AS NVARCHAR(10)) + N'>' + CAST(ChildItemId AS NVARCHAR(10)) + N'>' AS NVARCHAR(400))
FROM #CycBom WHERE ParentItemId = 1
UNION ALL
SELECT b.ParentItemId, b.ChildItemId, CAST(e.Chain + CAST(b.ChildItemId AS NVARCHAR(10)) + N'>' AS NVARCHAR(400))
FROM E e JOIN #CycBom b ON b.ParentItemId = e.ChildItemId
WHERE CHARINDEX(N'>' + CAST(b.ChildItemId AS NVARCHAR(10)) + N'>', e.Chain) = 0
)
SELECT CycleEdge = CAST(e.ChildItemId AS NVARCHAR(4)) + N' -> ' + CAST(b.ChildItemId AS NVARCHAR(4)), b.Note
FROM E e
JOIN #CycBom b ON b.ParentItemId = e.ChildItemId
WHERE CHARINDEX(N'>' + CAST(b.ChildItemId AS NVARCHAR(10)) + N'>', e.Chain) > 0
OPTION (MAXRECURSION 64);
GO
-- 04.7 MAXRECURSION 的默认上限（100）
PRINT N'----- 04.7 MAXRECURSION：默认 100 层的硬上限 -----';
DROP TABLE IF EXISTS #Chain;
SELECT g.value AS NodeId, NULLIF(g.value - 1, 0) AS ParentId
INTO #Chain
FROM GENERATE_SERIES(1, 200) AS g;
GO
PRINT N'[A] 200 层链，不加 OPTION -> 默认只允许 100 层';
BEGIN TRY
WITH C AS (
SELECT NodeId, Lvl = CAST(1 AS INT) FROM #Chain WHERE ParentId IS NULL
UNION ALL
SELECT c.NodeId, p.Lvl + 1 FROM #Chain c JOIN C p ON c.ParentId = p.NodeId
)
SELECT MaxLvl = MAX(Lvl) FROM C;
END TRY
BEGIN CATCH
SELECT Result = N'被中止', ErrNo = ERROR_NUMBER(), ErrMsg = LEFT(ERROR_MESSAGE(), 200);
END CATCH
GO
PRINT N'[B] 显式抬到 300 层 -> 正常通过';
WITH C AS (
SELECT NodeId, Lvl = CAST(1 AS INT) FROM #Chain WHERE ParentId IS NULL
UNION ALL
SELECT c.NodeId, p.Lvl + 1 FROM #Chain c JOIN C p ON c.ParentId = p.NodeId
)
SELECT MaxLvl = MAX(Lvl), NodeCnt = COUNT(*) FROM C OPTION (MAXRECURSION 300);
GO
PRINT N'[C] 上限就是 32767：再大必须写 0（0 = 不限制，生产上很危险）';
-- ★ 这个报错发生在编译期，静态批次里 TRY/CATCH 抓不住（批次会直接中断），
-- 必须塞进动态 SQL 才能拿到干净的结果行 —— 这是第六节那条结论的又一个例证。
DECLARE @sqlMax NVARCHAR(MAX) = N'
WITH C AS (
SELECT NodeId, Lvl = CAST(1 AS INT) FROM #Chain WHERE ParentId IS NULL
UNION ALL
SELECT c.NodeId, p.Lvl + 1 FROM #Chain c JOIN C p ON c.ParentId = p.NodeId
)
SELECT NodeCnt = COUNT(*) FROM C OPTION (MAXRECURSION 40000);';
BEGIN TRY
EXEC sp_executesql @sqlMax;
SELECT Result = N'意外通过';
END TRY
BEGIN CATCH
SELECT Result = N'被拒绝', ErrNo = ERROR_NUMBER(), ErrMsg = LEFT(ERROR_MESSAGE(), 260);
END CATCH
GO
/* ================================================================================
05 CRM 推荐人层级（老带新）
================================================================================ */
PRINT N'';
PRINT N'===== 05 CRM 推荐人层级 =====';
GO
IF COL_LENGTH(N'crm.Customer', N'ReferrerId') IS NULL ALTER TABLE crm.Customer ADD ReferrerId INT NULL;
IF COL_LENGTH(N'crm.Customer', N'ReferralLvl') IS NULL ALTER TABLE crm.Customer ADD ReferralLvl TINYINT NULL;
GO
-- 构建推荐关系：前 20 位是自然到店客（无推荐人），其余由序号 1/3 的人推荐。
-- 因为 n/3 < n 恒成立，所以天然不可能出现环 —— 这是「按序号构造树」的一个好处。
UPDATE c SET c.ReferrerId = CASE WHEN c.CustomerId <= 20 THEN NULL ELSE c.CustomerId / 3 END
FROM crm.Customer c;
GO
WITH Ref AS (
SELECT c.CustomerId, CAST(1 AS TINYINT) AS Lvl
FROM crm.Customer c WHERE c.ReferrerId IS NULL
UNION ALL
SELECT c.CustomerId, CAST(r.Lvl + 1 AS TINYINT)
FROM crm.Customer c JOIN Ref r ON c.ReferrerId = r.CustomerId
)
UPDATE c SET c.ReferralLvl = r.Lvl
FROM crm.Customer c JOIN Ref r ON r.CustomerId = c.CustomerId;
GO
SELECT RootCnt = SUM(CASE WHEN ReferrerId IS NULL THEN 1 ELSE 0 END),
MaxDepth = MAX(ReferralLvl), Nodes = COUNT(*)
FROM crm.Customer;
GO
-- 05.1 某个会员的完整下拉（多级下线）
PRINT N'----- 05.1 会员 9（一棵小树）的完整下线列表 -----';
DECLARE @m INT = 9;
WITH Down AS (
SELECT c.CustomerId, c.CustomerName, c.Tier, c.ReferralLvl,
RelDepth = CAST(0 AS INT),
Indent = CAST(N'' AS NVARCHAR(60)),
SortPath = CAST(RIGHT(REPLICATE('0',6) + CAST(c.CustomerId AS VARCHAR(6)),6) AS NVARCHAR(400))
FROM crm.Customer c WHERE c.CustomerId = @m
UNION ALL
SELECT c.CustomerId, c.CustomerName, c.Tier, c.ReferralLvl,
d.RelDepth + 1,
CAST(REPLICATE(N' ', d.RelDepth + 1) AS NVARCHAR(60)),
CAST(d.SortPath + N'/' + RIGHT(REPLICATE('0',6) + CAST(c.CustomerId AS VARCHAR(6)),6) AS NVARCHAR(400))
FROM crm.Customer c JOIN Down d ON c.ReferrerId = d.CustomerId
)
SELECT TOP 20 RelDepth, Member = CAST(Indent + CustomerName AS NVARCHAR(80)), Tier, GlobalLvl = ReferralLvl
FROM Down ORDER BY SortPath;
GO
-- 05.2 直推 / 间推人数统计
PRINT N'----- 05.2 推荐人业绩榜：直推 / 间推 / 总下线 -----';
-- ★ 别写成 SUM(CASE WHEN (SELECT COUNT(*) ...) = 1 ...)：
-- 聚合函数里再套子查询会直接报 130，不管外面是 CASE 还是别的写法。
-- 正确做法是让递归自己带上「第几代」这一列，再按代次分类汇总。
WITH Direct AS (
SELECT ReferrerId AS AncestorId, CustomerId AS DescId
FROM crm.Customer WHERE ReferrerId IS NOT NULL
),
AllDown AS (
SELECT d.AncestorId, d.DescId, Gen = CAST(1 AS INT)
FROM Direct d
UNION ALL
SELECT a.AncestorId, ch.CustomerId, a.Gen + 1
FROM AllDown a JOIN crm.Customer ch ON ch.ReferrerId = a.DescId
)
SELECT TOP 12
Referrer = r.CustomerName,
DirectCnt = SUM(CASE WHEN a.Gen = 1 THEN 1 ELSE 0 END),
IndirectCnt = SUM(CASE WHEN a.Gen > 1 THEN 1 ELSE 0 END),
DownlineCnt = COUNT(*),
MaxGeneration = MAX(a.Gen)
FROM AllDown a
JOIN crm.Customer r ON r.CustomerId = a.AncestorId
GROUP BY r.CustomerId, r.CustomerName
ORDER BY DownlineCnt DESC;
GO
-- 05.3 层级深度分布
PRINT N'----- 05.3 推荐层级深度分布 -----';
SELECT
ReferralLvl,
MemberCnt = COUNT(*),
VIPCnt = SUM(CASE WHEN Tier = 'VIP' THEN 1 ELSE 0 END),
OldestJoin = MIN(RegisterDate),
NewestJoin = MAX(RegisterDate)
FROM crm.Customer
GROUP BY ReferralLvl
ORDER BY ReferralLvl;
GO
-- 05.4 分佣：沿祖先链向上逐级计提，比例 5% / 3% / 1%（三代封顶）
PRINT N'----- 05.4 分佣链路：某笔消费沿推荐链向上计提 -----';
DECLARE @buyer INT = 800; -- 800 -> 266 -> 88 -> 29 -> 9，四代都在
DECLARE @amount DECIMAL(18,2) = 86000.00;
DECLARE @gen TABLE (Gen INT PRIMARY KEY, RatePct DECIMAL(9,4));
INSERT INTO @gen VALUES (1, 5.0), (2, 3.0), (3, 1.0);
WITH Up AS (
SELECT Cid = c.CustomerId, ParentCid = c.ReferrerId, Gen = CAST(1 AS INT)
FROM crm.Customer c WHERE c.CustomerId = (SELECT ReferrerId FROM crm.Customer WHERE CustomerId = @buyer)
UNION ALL
SELECT c.CustomerId, c.ReferrerId, u.Gen + 1
FROM crm.Customer c JOIN Up u ON c.CustomerId = u.ParentCid
WHERE u.Gen < 5
)
SELECT
Generation = u.Gen,
Referrer = c.CustomerName,
Tier = c.Tier,
RatePct = g.RatePct,
Commission = CAST(@amount * g.RatePct / 100.0 AS DECIMAL(18,2))
FROM Up u
JOIN crm.Customer c ON c.CustomerId = u.Cid
JOIN @gen g ON g.Gen = u.Gen
ORDER BY u.Gen;
GO
-- 05.5 脏数据检测：ReferrerId 指向一个不存在的会员（孤立子树）
PRINT N'----- 05.5 脏数据：推荐人指向不存在的会员 -----';
UPDATE crm.Customer SET ReferrerId = 999999 WHERE CustomerId = 500;
GO
WITH Reach AS (
SELECT c.CustomerId FROM crm.Customer c WHERE c.ReferrerId IS NULL
UNION ALL
SELECT c.CustomerId FROM crm.Customer c JOIN Reach r ON c.ReferrerId = r.CustomerId
)
SELECT
TotalMembers = (SELECT COUNT(*) FROM crm.Customer),
ReachableFromRoots = (SELECT COUNT(*) FROM Reach),
Orphans = (SELECT COUNT(*) FROM crm.Customer) - (SELECT COUNT(*) FROM Reach),
WorstCase = N'孤儿子树永远不会出现在任何下线报表里，且没有报错';
GO
UPDATE crm.Customer SET ReferrerId = 500 / 3 WHERE CustomerId = 500;
GO
/* ================================================================================
06 递归 CTE 的硬限制（实测矩阵）
---------------------------------------------------------------------------
重要发现（已在本机 17.0.1135.8 上验证）：
递归成员的这些限制属于【编译期绑定错误】，直接写在静态批次里时
TRY/CATCH 捕获不到，批次会直接中断。只有把它们包进动态 SQL
（sp_executesql）之后，TRY/CATCH 才能捕获并继续。
所以生产脚本里做「能力探测」时，必须用动态 SQL。
================================================================================ */
PRINT N'';
PRINT N'===== 06 递归 CTE 硬限制实测矩阵 =====';
GO
DROP TABLE IF EXISTS #RcTest;
CREATE TABLE #RcTest (
Seq INT IDENTITY(1,1),
CaseName NVARCHAR(90),
Verdict NVARCHAR(12),
ErrNo INT NULL,
ErrMsg NVARCHAR(260) NULL
);
GO
DROP TABLE IF EXISTS #pn;
CREATE TABLE #pn (id INT PRIMARY KEY, pid INT NULL);
INSERT INTO #pn (id, pid) VALUES (1, NULL), (2, 1), (3, 1), (4, 2);
GO
DECLARE @sql NVARCHAR(MAX), @name NVARCHAR(90);
-- 用例 1：递归成员里用 LEFT JOIN（外部联接）
SET @name = N'递归成员用 LEFT JOIN（外部联接）';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, t.L + 1 FROM #pn c LEFT JOIN T t ON c.pid = t.id WHERE t.id IS NOT NULL)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 2：递归成员里用 TOP
SET @name = N'递归成员用 TOP';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT TOP 50 c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 3：递归成员里用 GROUP BY / 聚合
SET @name = N'递归成员用 GROUP BY / 聚合函数';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.pid, COUNT(*) FROM #pn c JOIN T t ON c.pid = t.id GROUP BY c.pid)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 4：递归成员里用 STRING_AGG（本质仍是聚合）
SET @name = N'递归成员用 STRING_AGG';
SET @sql = N'WITH T AS (SELECT id, CAST(N'''' AS NVARCHAR(200)) AS P FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, STRING_AGG(CAST(t.id AS NVARCHAR(9)), N''/'') FROM #pn c JOIN T t ON c.pid = t.id GROUP BY c.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 5：递归成员里用 DISTINCT
SET @name = N'递归成员用 DISTINCT';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT DISTINCT c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 6：用 UNION 代替 UNION ALL
SET @name = N'用 UNION 代替 UNION ALL';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION SELECT c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 7：锚点与递归成员列类型不一致
SET @name = N'锚点 VARCHAR / 递归 NVARCHAR 类型不一致';
SET @sql = N'WITH T AS (SELECT id, CAST(N'''''' AS VARCHAR(50)) AS P FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, CAST(N'''''' AS NVARCHAR(50)) FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 8：OPTION (MAXRECURSION 0) —— 不限制（危险但合法）
SET @name = N'OPTION (MAXRECURSION 0) 不限制层数';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 0);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 9：外层查询多次引用同一个递归 CTE
SET @name = N'外层多次引用同一个递归 CTE';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
SELECT (SELECT COUNT(*) FROM T) AS A, (SELECT MAX(L) FROM T) AS B;';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 10：递归 CTE 出现在 JOIN 的右侧
SET @name = N'递归 CTE 放在 JOIN 右侧';
SET @sql = N'WITH T AS (SELECT id FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id FROM #pn c JOIN T t ON c.pid = t.id)
SELECT COUNT(*) FROM #pn x JOIN T t ON t.id = x.id;';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 11：递归成员里引用 CTE 自身两次（多重递归引用）
SET @name = N'递归成员里两次引用 CTE 自身';
SET @sql = N'WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, a.L + b.L FROM #pn c JOIN T a ON c.pid = a.id JOIN T b ON b.id = a.id)
SELECT COUNT(*) FROM T OPTION (MAXRECURSION 16);';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
-- 用例 12：递归 CTE 里做 UPDATE（在 CTE 外对递归结果更新）
SET @name = N'对递归 CTE 结果做 UPDATE';
SET @sql = N'CREATE TABLE #up (id INT PRIMARY KEY, lvl INT NULL);
INSERT INTO #up (id, lvl) VALUES (1, NULL), (2, NULL), (3, NULL);
WITH T AS (SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL SELECT c.id, t.L + 1 FROM #pn c JOIN T t ON c.pid = t.id)
UPDATE u SET u.lvl = t.L FROM #up u JOIN T t ON t.id = u.id;';
BEGIN TRY EXEC sp_executesql @sql; INSERT INTO #RcTest VALUES (@name, N'可用', NULL, NULL);
END TRY BEGIN CATCH INSERT INTO #RcTest VALUES (@name, N'被拒', ERROR_NUMBER(), LEFT(ERROR_MESSAGE(), 250)); END CATCH
SELECT Seq, CaseName, Verdict, ErrNo, ErrMsg FROM #RcTest ORDER BY Seq;
GO
PRINT N'';
PRINT N'----- 06-B 同一批限制用【静态批次】写会怎样：TRY/CATCH 捕获不到，批次直接中断 -----';
GO
PRINT N'[静态批次开始] 下面这条语句会报 462 并中断本批次，注意后面那行 PRINT 不会输出';
BEGIN TRY
WITH T AS (
SELECT id, CAST(1 AS INT) AS L FROM #pn WHERE pid IS NULL
UNION ALL
SELECT c.id, t.L + 1 FROM #pn c LEFT JOIN T t ON c.pid = t.id
)
SELECT COUNT(*) FROM T;
END TRY
BEGIN CATCH
PRINT N'[如果看到这一行，说明 CATCH 生效了]';
END CATCH
PRINT N'[静态批次结束] 这一行不会出现 -> 证明编译期错误 TRY/CATCH 拦不住';
GO
PRINT N'[恢复执行] 新批次照常运行。生产脚本里做探测必须走动态 SQL。';
GO
/* ================================================================================
07 万亿级高并发：层级维表 + 事实表的实测对比
---------------------------------------------------------------------------
先建立一个认知：层级维表本身永远是小表。
组织架构：几千到几万行
商品品类：几千行
BOM：百万级（但深度只有 5-8 层，且按成品分区后每个成品只有几十行）
真正到万亿的是【事实表】。所以问题不是「递归慢」，而是：
1) 递归部分强制串行 —— 它会阻止整个执行计划并行化
2) 递归结果无法下推谓词 —— 优化器看不到它能用哪些索引
3) 递归 CTE 只能行模式，拿不到列存储的批处理模式优势
================================================================================ */
PRINT N'';
PRINT N'===== 07 万亿级高并发方案 =====';
GO
-- ---------------------------------------------------------------------------
-- 7.1 造一棵 65535 节点的完全二叉树（16 层），用来放大递归的成本
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS rpt.BigNodeClosure;
DROP TABLE IF EXISTS rpt.BigNode;
GO
CREATE TABLE rpt.BigNode (
NodeId INT NOT NULL,
ParentId INT NULL,
Path NVARCHAR(200) NULL,
CONSTRAINT PK_BigNode PRIMARY KEY CLUSTERED (NodeId)
);
GO
INSERT INTO rpt.BigNode WITH (TABLOCK) (NodeId, ParentId)
SELECT g.value, NULLIF(g.value / 2, 0)
FROM GENERATE_SERIES(1, 65535) AS g;
GO
CREATE INDEX IX_BigNode_Parent ON rpt.BigNode (ParentId) INCLUDE (NodeId);
GO
-- ---------------------------------------------------------------------------
-- 7.2 闭包表：用「逐层加宽」的方式构建，比递归 CTE 快得多
-- 每轮把上一轮的 (祖先, 后代) 对再往下接一层。
-- 16 轮就跑完全树，行数约 98 万。
-- ---------------------------------------------------------------------------
CREATE TABLE rpt.BigNodeClosure (
AncestorId INT NOT NULL,
DescendantId INT NOT NULL,
Depth INT NOT NULL,
CONSTRAINT PK_BigNodeClosure PRIMARY KEY CLUSTERED (AncestorId, DescendantId)
);
GO
CREATE INDEX IX_BigNodeClosure_Depth ON rpt.BigNodeClosure (Depth, DescendantId) INCLUDE (AncestorId);
GO
INSERT INTO rpt.BigNodeClosure (AncestorId, DescendantId, Depth)
SELECT NodeId, NodeId, 0 FROM rpt.BigNode;
GO
DECLARE @d INT = 0, @ins INT = 1;
WHILE @ins > 0 AND @d < 64
BEGIN
INSERT INTO rpt.BigNodeClosure (AncestorId, DescendantId, Depth)
SELECT c.AncestorId, n.NodeId, @d + 1
FROM rpt.BigNodeClosure c
JOIN rpt.BigNode n ON n.ParentId = c.DescendantId
WHERE c.Depth = @d;
SET @ins = @@ROWCOUNT;
SET @d += 1;
END
SELECT ClosureRows = COUNT(*) FROM rpt.BigNodeClosure;
GO
-- 7.3 顺带把物化路径也从闭包表刷出来（比递归 CTE 快，且天然是有序的）
UPDATE n
SET n.Path = p.Path
FROM rpt.BigNode n
JOIN (
SELECT c.DescendantId,
Path = N'/' + STRING_AGG(CAST(c.AncestorId AS NVARCHAR(10)), N'/')
WITHIN GROUP (ORDER BY c.Depth DESC) + N'/'
FROM rpt.BigNodeClosure c
GROUP BY c.DescendantId
) p ON p.DescendantId = n.NodeId;
GO
CREATE INDEX IX_BigNode_Path ON rpt.BigNode (Path);
GO
SELECT DeepestPath = CAST(Path AS NVARCHAR(80)), PathSegments = LEN(Path) - LEN(REPLACE(Path, N'/', N'')) - 1
FROM rpt.BigNode WHERE NodeId = 65535;
GO
-- ---------------------------------------------------------------------------
-- 7.4 读性能对比：子树 / 祖先链
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.4-A 取节点 1 的完整子树（65535 行），递归 CTE vs 闭包表 vs 物化路径 -----';
GO
PRINT N'[A1] 递归 CTE 展开子树';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH Sub AS (
SELECT NodeId FROM rpt.BigNode WHERE NodeId = 1
UNION ALL
SELECT c.NodeId FROM rpt.BigNode c JOIN Sub s ON c.ParentId = s.NodeId
)
SELECT SubtreeRows = COUNT(*) FROM Sub OPTION (MAXRECURSION 64);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT N'[A2] 闭包表（一次索引查找）';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SubtreeRows = COUNT(*) FROM rpt.BigNodeClosure WHERE AncestorId = 1;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT N'[A3] 物化路径 LIKE 前缀';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SubtreeRows = COUNT(*) FROM rpt.BigNode WHERE Path = N'/1/' OR Path LIKE N'/1/%';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT N'';
PRINT N'----- 7.4-B 取最深叶子（65535）的祖先链（16 行）-----';
GO
PRINT N'[B1] 递归 CTE 向上回溯';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH Up AS (
SELECT NodeId, ParentId FROM rpt.BigNode WHERE NodeId = 65535
UNION ALL
SELECT p.NodeId, p.ParentId FROM rpt.BigNode p JOIN Up c ON p.NodeId = c.ParentId
)
SELECT AncestorRows = COUNT(*) FROM Up OPTION (MAXRECURSION 64);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
PRINT N'[B2] 闭包表反向';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT AncestorRows = COUNT(*) FROM rpt.BigNodeClosure WHERE DescendantId = 65535;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
-- ---------------------------------------------------------------------------
-- 7.5 造事实表：500 万行，带冗余扁平层级列
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.5 构建事实表 rpt.OrgSalesFact（500 万行，含冗余层级列）-----';
GO
DROP TABLE IF EXISTS rpt.OrgSalesFact;
GO
CREATE TABLE rpt.OrgSalesFact (
FactId BIGINT IDENTITY(1,1) NOT NULL,
OrgUnitId INT NOT NULL, -- 归到门店级组织
OrgL1Id INT NOT NULL, -- 冗余：集团
OrgL2Id INT NOT NULL, -- 冗余：大区
OrgL3Id INT NOT NULL, -- 冗余：国家/地区
OrgL4Id INT NOT NULL, -- 冗余：城市
CategoryId INT NOT NULL,
OrderDate DATE NOT NULL,
Qty INT NOT NULL,
AmountUsd DECIMAL(18,2) NOT NULL
);
GO
-- 门店级组织的层级映射（10 家门店 x 5 层）
DROP TABLE IF EXISTS #StoreIds;
SELECT StoreOrgId = OrgUnitId, rn = ROW_NUMBER() OVER (ORDER BY OrgUnitId)
INTO #StoreIds
FROM hr.OrgUnit WHERE StoreCode IS NOT NULL;
GO
DROP TABLE IF EXISTS #StoreLv;
SELECT
oc.DescendantId AS StoreOrgId,
OrgL1Id = MAX(CASE WHEN a.Lvl = 1 THEN oc.AncestorId END),
OrgL2Id = MAX(CASE WHEN a.Lvl = 2 THEN oc.AncestorId END),
OrgL3Id = MAX(CASE WHEN a.Lvl = 3 THEN oc.AncestorId END),
OrgL4Id = MAX(CASE WHEN a.Lvl = 4 THEN oc.AncestorId END)
INTO #StoreLv
FROM hr.OrgUnitClosure oc
JOIN hr.OrgUnit st ON st.OrgUnitId = oc.DescendantId AND st.StoreCode IS NOT NULL
JOIN hr.OrgUnit a ON a.OrgUnitId = oc.AncestorId
GROUP BY oc.DescendantId;
GO
DROP TABLE IF EXISTS #LeafCats;
SELECT CategoryId, rn = ROW_NUMBER() OVER (ORDER BY CategoryId)
INTO #LeafCats
FROM erp.ProductCategory WHERE IsLeaf = 1;
GO
DECLARE @leafCnt INT = (SELECT COUNT(*) FROM #LeafCats);
DECLARE @storeCnt INT = (SELECT COUNT(*) FROM #StoreIds);
DECLARE @factRows INT = 5000000;
INSERT INTO rpt.OrgSalesFact WITH (TABLOCK)
(OrgUnitId, OrgL1Id, OrgL2Id, OrgL3Id, OrgL4Id, CategoryId, OrderDate, Qty, AmountUsd)
SELECT
s.StoreOrgId, lv.OrgL1Id, lv.OrgL2Id, lv.OrgL3Id, lv.OrgL4Id,
c.CategoryId,
DATEADD(DAY, g.value % 730, CAST('2024-01-01' AS DATE)),
(g.value % 4) + 1,
CAST((g.value % 9973) * 7.31 + 180 AS DECIMAL(18,2))
FROM GENERATE_SERIES(1, @factRows) AS g
JOIN #StoreIds s ON s.rn = (g.value % @storeCnt) + 1
JOIN #StoreLv lv ON lv.StoreOrgId = s.StoreOrgId
JOIN #LeafCats c ON c.rn = (g.value % @leafCnt) + 1
OPTION (MAXDOP 6);
GO
SELECT FactRows = COUNT(*) FROM rpt.OrgSalesFact;
GO
-- 列存储索引（分析型负载的标配）
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrgSalesFact ON rpt.OrgSalesFact WITH (MAXDOP = 6);
GO
SELECT index_name = i.name, i.type_desc FROM sys.indexes i WHERE i.object_id = OBJECT_ID('rpt.OrgSalesFact');
GO
-- ---------------------------------------------------------------------------
-- 7.6 关键对比：R-GCN（大中华区）的销售额，四种取组织范围的方式
-- 四种写法的结果必须完全一致，只有计划和耗时不同
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.6 事实表关联实测：递归 CTE 求范围 vs 闭包表 vs 物化路径 vs 冗余扁平列 -----';
PRINT N' 判定并行度的土办法：CPU 时间明显大于占用时间 => 多线程；两者接近 => 单线程。';
GO
DECLARE @gcn INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN');
DECLARE @gcnPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @gcn);
PRINT N'[方式1 反例] 递归 CTE 现场求子树 + JOIN 事实表';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
WITH Sub AS (
SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgUnitId = @gcn
UNION ALL
SELECT c.OrgUnitId FROM hr.OrgUnit c JOIN Sub s ON c.ParentId = s.OrgUnitId
)
SELECT c.CategoryName, OrderCnt = COUNT_BIG(*), AmountUsd = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN Sub s ON s.OrgUnitId = f.OrgUnitId
JOIN erp.ProductCategory c ON c.CategoryId = f.CategoryId
GROUP BY c.CategoryName
ORDER BY AmountUsd DESC;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
PRINT N'[方式2] 闭包表 JOIN（预计算好的祖孙对）';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT c.CategoryName, OrderCnt = COUNT_BIG(*), AmountUsd = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN hr.OrgUnitClosure oc ON oc.DescendantId = f.OrgUnitId AND oc.AncestorId = @gcn
JOIN erp.ProductCategory c ON c.CategoryId = f.CategoryId
GROUP BY c.CategoryName
ORDER BY AmountUsd DESC;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
PRINT N'[方式3] 事实表冗余物化路径 + LIKE 前缀';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT c.CategoryName, OrderCnt = COUNT_BIG(*), AmountUsd = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN hr.OrgUnit u ON u.OrgUnitId = f.OrgUnitId
JOIN erp.ProductCategory c ON c.CategoryId = f.CategoryId
WHERE u.Path = @gcnPath OR u.Path LIKE @gcnPath + N'%'
GROUP BY c.CategoryName
ORDER BY AmountUsd DESC;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
PRINT N'[方式4 正例] 事实表冗余扁平层级列（单表过滤，可批处理、可并行）';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT c.CategoryName, OrderCnt = COUNT_BIG(*), AmountUsd = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN erp.ProductCategory c ON c.CategoryId = f.CategoryId
WHERE f.OrgL2Id = @gcn
GROUP BY c.CategoryName
ORDER BY AmountUsd DESC;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
GO
-- 7.6-X 结果一致性自检：四种写法必须得出同一个总额
PRINT N'';
PRINT N'----- 7.6-X 一致性自检：四种写法的总额必须完全相同 -----';
GO
DECLARE @gcn INT = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN');
DECLARE @gcnPath NVARCHAR(400) = (SELECT Path FROM hr.OrgUnit WHERE OrgUnitId = @gcn);
-- ★ 递归 CTE 必须提到语句最外层：塞进 IN (...) 子查询里会报 156 + 319 + 102
WITH Sub AS (
SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgUnitId = @gcn
UNION ALL
SELECT c.OrgUnitId FROM hr.OrgUnit c JOIN Sub s ON c.ParentId = s.OrgUnitId
)
SELECT Method = N'递归 CTE（反例）', Amount = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f JOIN Sub s ON s.OrgUnitId = f.OrgUnitId
UNION ALL
SELECT N'闭包表', CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN hr.OrgUnitClosure oc ON oc.DescendantId = f.OrgUnitId AND oc.AncestorId = @gcn
UNION ALL
SELECT N'物化路径', CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN hr.OrgUnit u ON u.OrgUnitId = f.OrgUnitId
WHERE u.Path = @gcnPath OR u.Path LIKE @gcnPath + N'%'
UNION ALL
SELECT N'冗余扁平列（正例）', CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
WHERE f.OrgL2Id = @gcn
OPTION (MAXRECURSION 64);
GO
-- ---------------------------------------------------------------------------
-- 7.7 把层级维表物化成「报表可直接 JOIN 的宽表」
-- 这就是应用侧要缓存进 Redis / 内存表的那一份
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.7 层级维度宽表 rpt.OrgHierarchyFlat（报表与缓存的标准产物）-----';
GO
DROP TABLE IF EXISTS rpt.OrgHierarchyFlat;
GO
SELECT
AncestorId = c.AncestorId,
AncestorName = a.OrgName,
AncestorLvl = a.Lvl,
AncestorType = a.OrgType,
DescendantId = c.DescendantId,
DescName = d.OrgName,
DescType = d.OrgType,
DescStore = d.StoreCode,
Depth = c.Depth,
RelationType = CASE WHEN c.Depth = 0 THEN N'SELF'
WHEN c.Depth = 1 THEN N'DIRECT'
ELSE N'INDIRECT' END,
RootToLeafPath = N'/' + a.OrgName + N'/' + d.OrgName + N'/'
INTO rpt.OrgHierarchyFlat
FROM hr.OrgUnitClosure c
JOIN hr.OrgUnit a ON a.OrgUnitId = c.AncestorId
JOIN hr.OrgUnit d ON d.OrgUnitId = c.DescendantId;
GO
CREATE CLUSTERED INDEX CIX_OrgHierarchyFlat ON rpt.OrgHierarchyFlat (AncestorId, Depth, DescendantId);
CREATE INDEX IX_OrgHierarchyFlat_Desc ON rpt.OrgHierarchyFlat (DescendantId, Depth);
GO
SELECT FlatRows = COUNT(*), SelfRows = SUM(CASE WHEN Depth = 0 THEN 1 ELSE 0 END) FROM rpt.OrgHierarchyFlat;
GO
SELECT TOP 8 * FROM rpt.OrgHierarchyFlat WHERE AncestorId = (SELECT OrgUnitId FROM hr.OrgUnit WHERE OrgCode = N'R-GCN') ORDER BY Depth, DescendantId;
GO
-- ---------------------------------------------------------------------------
-- 7.8 标量 UDF：层级维表上的头号性能杀手
-- 递归 CTE 天生无法被内联，所以「用 UDF 递归求祖先名」= 每行一次过程调用。
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.8 标量 UDF 求祖先名：递归版 vs 查表版 -----';
GO
CREATE OR ALTER FUNCTION dbo.fn_OrgAncestorNameRecursive(@OrgUnitId INT, @Lvl TINYINT)
RETURNS NVARCHAR(60)
AS
BEGIN
DECLARE @r NVARCHAR(60);
WITH Up AS (
SELECT OrgUnitId, ParentId, Lvl, OrgName FROM hr.OrgUnit WHERE OrgUnitId = @OrgUnitId
UNION ALL
SELECT p.OrgUnitId, p.ParentId, p.Lvl, p.OrgName
FROM hr.OrgUnit p JOIN Up u ON p.OrgUnitId = u.ParentId
)
SELECT @r = OrgName FROM Up WHERE Lvl = @Lvl;
RETURN @r;
END
GO
CREATE OR ALTER FUNCTION dbo.fn_OrgAncestorNameFlat(@OrgUnitId INT, @Lvl TINYINT)
RETURNS NVARCHAR(60)
AS
BEGIN
RETURN (SELECT TOP 1 AncestorName FROM rpt.OrgHierarchyFlat
WHERE DescendantId = @OrgUnitId AND AncestorLvl = @Lvl);
END
GO
SELECT
FunctionName = OBJECT_NAME(object_id),
IsInlineable = is_inlineable,
Diagnosis = CASE WHEN is_inlineable = 1 THEN N'会被优化器内联展开（快）'
ELSE N'无法内联，每行一次过程调用（慢）' END
FROM sys.sql_modules
WHERE object_id IN (OBJECT_ID('dbo.fn_OrgAncestorNameRecursive'), OBJECT_ID('dbo.fn_OrgAncestorNameFlat'));
GO
PRINT N'[UDF-1] 递归版标量 UDF（5 万行）-- 不能内联';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT AncestorName = dbo.fn_OrgAncestorNameRecursive(f.OrgUnitId, 2), Rows_ = COUNT_BIG(*),
Amount = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
WHERE f.FactId <= 50000
GROUP BY dbo.fn_OrgAncestorNameRecursive(f.OrgUnitId, 2);
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
PRINT N'[UDF-2] 查表版标量 UDF（同样 5 万行）-- 可内联';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT AncestorName = dbo.fn_OrgAncestorNameFlat(f.OrgUnitId, 2), Rows_ = COUNT_BIG(*),
Amount = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
WHERE f.FactId <= 50000
GROUP BY dbo.fn_OrgAncestorNameFlat(f.OrgUnitId, 2);
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
PRINT N'[UDF-3] 干脆别用 UDF：直接 JOIN 宽表';
SET STATISTICS IO ON; SET STATISTICS TIME ON;
SELECT AncestorName = h.AncestorName, Rows_ = COUNT_BIG(*),
Amount = CAST(SUM(f.AmountUsd) AS DECIMAL(20,2))
FROM rpt.OrgSalesFact f
JOIN rpt.OrgHierarchyFlat h ON h.DescendantId = f.OrgUnitId AND h.AncestorLvl = 2
WHERE f.FactId <= 50000
GROUP BY h.AncestorName;
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
GO
-- ---------------------------------------------------------------------------
-- 7.9 选型矩阵（可直接抄进设计文档）
-- ---------------------------------------------------------------------------
PRINT N'';
PRINT N'----- 7.9 层级存储选型矩阵 -----';
GO
SELECT * FROM (VALUES
(1, N'邻接表', N'O(1)', N'递归 CTE / 递归查询', N'几千行', N'目录树、组织架构', N'万能基础款，但别上去就递归'),
(2, N'邻接表 + 物化路径', N'O(1)', N'O(子树大小)', N'几万行', N'品类树、菜单、BOM 展开', N'LIKE 前缀要记得带上「自身」那个 OR'),
(3, N'闭包表', N'O(深度)', N'O(子树 x 祖先数)', N'几十万行', N'组织架构、权限域、分佣链路', N'读写最均衡，生产首选；可整体缓存'),
(4, N'嵌套集', N'O(1)', N'O(整表)', N'几千行', N'几乎不变的分类（如国标科目）', N'读最快写最贵，只在只读字典里用'),
(5, N'hierarchyid', N'O(1)', N'O(子树大小)', N'几万行', N'存量系统改造', N'微软已不建议新项目使用'),
(6, N'事实表冗余扁平层级列', N'O(1)', N'不适用（无层级表）', N'无上限', N'万亿级事实表的组织/品类过滤', N'配合列存储与分区，唯一能压住万亿行的路子'),
(7, N'图结构 (MATCH/NODE)', N'O(1)', N'O(1)', N'可变', N'Schema 频繁变化的血缘图', N'SQL Server 不支持变长路径，层级遍历用不了')
) v(SortNo, Model, ReadCost, WriteCost, ScaleHint, BestFor, Caveat)
ORDER BY SortNo;
GO
PRINT N'';
PRINT N'----- 7.10 深递归防护清单 -----';
GO
SELECT * FROM (VALUES
(1, N'永远显式写 OPTION (MAXRECURSION n)', N'默认只有 100 层，深树直接报 530；上限 32767'),
(2, N'不要写 MAXRECURSION 0', N'等于不限制，坏数据能让查询跑到超时拖垮实例'),
(3, N'递归成员里禁止聚合/TOP/DISTINCT/外部联接', N'编译期报 460/461/462/467，静态批次里 TRY/CATCH 还拦不住'),
(4, N'带路径做环检测', N'CHARINDEX(子节点标记, 祖先链) = 0 才继续下钻'),
(5, N'给父节点列建索引', N'递归 CTE 默认走嵌套循环，没有索引就是全表扫 N 次'),
(6, N'限制拼接字符串长度', N'路径列长度写小会静默截断或报 8152'),
(7, N'高频路径改查闭包表', N'读多写少的层级，一次索引查找换掉整棵树的递归'),
(8, N'事实表冗余层级路径', N'这是万亿级唯一可行的组织范围过滤方式'),
(9, N'层级维表整体缓存', N'几万行的小表放 Redis / 内存优化表，彻底绕开数据库层级计算')
) v(SeqNo, RuleText, Why);
GO
/* ================================================================================
08 验证与收尾
================================================================================ */
PRINT N'';
PRINT N'===== 08 验证 =====';
GO
PRINT N'[V1] 组织架构：层级分布必须与预期一致（1 根 / 5 层地理线 / 4 层职能线）';
SELECT Lvl, OrgType, NodeCnt = COUNT(*) FROM hr.OrgUnit GROUP BY Lvl, OrgType ORDER BY Lvl, OrgType;
GO
PRINT N'[V2] 闭包表不变式：每组 (祖先, 后代) 必须唯一且 Depth 与路径层数吻合';
SELECT
DuplicatePairs = (SELECT COUNT(*) FROM (SELECT AncestorId, DescendantId FROM hr.OrgUnitClosure
GROUP BY AncestorId, DescendantId HAVING COUNT(*) > 1) d),
SelfPairs = (SELECT COUNT(*) FROM hr.OrgUnitClosure WHERE AncestorId = DescendantId AND Depth <> 0),
MissingSelf = (SELECT COUNT(*) FROM hr.OrgUnit u
WHERE NOT EXISTS (SELECT 1 FROM hr.OrgUnitClosure c
WHERE c.AncestorId = u.OrgUnitId AND c.DescendantId = u.OrgUnitId AND c.Depth = 0)),
ClosureRows = (SELECT COUNT(*) FROM hr.OrgUnitClosure);
GO
PRINT N'[V3] 嵌套集不变式：(Rgt - Lft + 1) / 2 - 1 必须等于后代数';
SELECT BadNodes = COUNT(*)
FROM hr.OrgUnitNestedSet ns
JOIN (SELECT AncestorId, DescendantCnt = COUNT(*) - 1 FROM hr.OrgUnitClosure GROUP BY AncestorId) d
ON d.AncestorId = ns.OrgUnitId
WHERE (ns.Rgt - ns.Lft + 1) / 2 - 1 <> d.DescendantCnt;
GO
PRINT N'[V4] 嵌套集区间必须互不交叉（任意两个节点的区间要么包含要么相离）';
SELECT CrossingPairs = COUNT(*)
FROM hr.OrgUnitNestedSet a
JOIN hr.OrgUnitNestedSet b ON a.OrgUnitId < b.OrgUnitId
WHERE (a.Lft < b.Lft AND a.Rgt > b.Lft AND a.Rgt < b.Rgt)
OR (b.Lft < a.Lft AND b.Rgt > a.Lft AND b.Rgt < a.Rgt);
GO
PRINT N'[V5] hierarchyid：NodePath 必须唯一且层级与 Lvl 一致';
SELECT
PathCnt = COUNT(*),
DistinctPaths = COUNT(DISTINCT NodePath),
LvlMismatch = SUM(CASE WHEN NodePath.GetLevel() + 1 <> Lvl THEN 1 ELSE 0 END)
FROM hr.OrgUnitHid;
GO
PRINT N'[V6] BOM：成品数 / 最大展开深度 / 展开行数 / 自引用边';
WITH Bom AS (
SELECT Root = i.ItemId, Node = i.ItemId, Lvl = CAST(0 AS INT)
FROM erp.Item i WHERE i.ItemType = N'成品'
UNION ALL
SELECT bm.Root, b.ChildItemId, bm.Lvl + 1
FROM Bom bm JOIN erp.BOMLine b ON b.ParentItemId = bm.Node
)
SELECT
FinishedItems = COUNT(DISTINCT Root),
MaxBomDepth = MAX(Lvl),
ExpandedRows = COUNT(*),
SelfRefEdges = (SELECT COUNT(*) FROM erp.BOMLine WHERE ParentItemId = ChildItemId),
PurchasedItems = (SELECT COUNT(*) FROM erp.Item WHERE IsPurchased = 1)
FROM Bom OPTION (MAXRECURSION 32);
GO
PRINT N'[V7] CRM：推荐树必须无环（ReferrerId 必须严格小于 CustomerId）';
SELECT Violations = COUNT(*) FROM crm.Customer WHERE ReferrerId IS NOT NULL AND ReferrerId >= CustomerId;
GO
PRINT N'[V8] 事实表与层级宽表的一致性：所有事实行都必须能在宽表里找到 SELF 关系';
SELECT OrphanFacts = COUNT(*)
FROM rpt.OrgSalesFact f
WHERE NOT EXISTS (SELECT 1 FROM rpt.OrgHierarchyFlat h
WHERE h.DescendantId = f.OrgUnitId AND h.Depth = 0);
GO
PRINT N'[V9] 大表体检';
SELECT TableName = N'rpt.BigNode', Rows_ = COUNT(*) FROM rpt.BigNode
UNION ALL SELECT N'rpt.BigNodeClosure', COUNT(*) FROM rpt.BigNodeClosure
UNION ALL SELECT N'rpt.OrgSalesFact', COUNT(*) FROM rpt.OrgSalesFact
UNION ALL SELECT N'hr.OrgUnit', COUNT(*) FROM hr.OrgUnit
UNION ALL SELECT N'hr.OrgUnitClosure', COUNT(*) FROM hr.OrgUnitClosure
UNION ALL SELECT N'erp.ProductCategory', COUNT(*) FROM erp.ProductCategory
UNION ALL SELECT N'erp.Item', COUNT(*) FROM erp.Item
UNION ALL SELECT N'erp.BOMLine', COUNT(*) FROM erp.BOMLine
UNION ALL SELECT N'rpt.OrgHierarchyFlat', COUNT(*) FROM rpt.OrgHierarchyFlat;
GO
PRINT N'';
PRINT N'===== 全部完成 =====';
GO
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号