在SQL Server中用多表构建含产品类别的分页树形结构

可以在同一递归脚本中构建完整的树形结构，通过扩展CTE（公用表表达式），将公司、产品类别、产品节点按层级拼接，同时处理节点关联与排序逻辑。以下是完整实现：

declare @companyID int = 1
;with Hierarchy (id, parentID, level, name, nodeType) as
(
   -- 起始根公司节点
   select 
      A.id, A.parentID, 0 as level, A.name, 'Company' as nodeType
   from @companies as A
   where A.id = @companyID
   union all
   -- 递归加载子公司节点
   select 
      A.id, A.parentID, B.level + 1, A.name, 'Company' as nodeType
   from @companies as A
   inner join Hierarchy as B on A.parentID = B.id
   union all
   -- 加载对应公司的产品类别节点（作为公司的子节点）
   select 
      -- 用负数ID区分类别节点，避免与公司ID冲突
      -ROW_NUMBER() over (partition by cp.companyID order by cp.category) as id,
      cp.companyID as parentID,
      B.level + 1 as level,
      cp.category as name,
      'Category' as nodeType
   from @companiesProducts as cp
   inner join Hierarchy as B on cp.companyID = B.id
   where B.nodeType = 'Company'
   group by cp.companyID, cp.category
   union all
   -- 加载对应类别的产品节点（作为类别的子节点）
   select 
      -- 用更负的ID区分产品节点
      -10000 - ROW_NUMBER() over (order by cp.category, cp.product) as id,
      -- 关联获取对应类别的虚拟父ID
      (select -ROW_NUMBER() over (partition by cp2.companyID order by cp2.category) 
       from @companiesProducts as cp2 
       where cp2.companyID = cp.companyID and cp2.category = cp.category) as parentID,
      (select level + 2 from Hierarchy where id = cp.companyID) as level,
      cp.product as name,
      'Product' as nodeType
   from @companiesProducts as cp
)
-- 生成带缩进的层级展示，并按规则排序
select 
   REPLICATE('  ', level) + name as full_hierarchy_path,
   id, parentID, level, name, nodeType
from Hierarchy
order by level, 
         case nodeType when 'Company' then 1 when 'Category' then 2 when 'Product' then 3 end,
         name

关键逻辑说明 #

1. 节点类型区分：新增nodeType字段标记节点是公司、类别还是产品，用于排序和层级关联
2. ID冲突避免：类别和产品节点使用负数ID，防止与现有公司ID重复，保证递归关联的正确性
3. 层级拼接：通过三次UNION ALL分别拼接根公司、子公司、产品类别、产品节点，形成完整层级
4. 可视化缩进：用REPLICATE(' ', level)生成缩进，直观展示嵌套结构
5. 排序规则：优先按层级排序，再按节点类型（公司→类别→产品）排序，最后按名称排序，确保结构符合预期

查询结果示例 #

full_hierarchy_path
-------------------------
Owner
  Acessories
    Keyboard
    Mouse
  Furnitues
    Chair
    Table
  Company A
    Acessories
        Microfone
  Company B
    Company BB
        Others
            Blabla

内容的提问来源于stack exchange，提问作者Cassiano