sql 遇到一个表拆分问题，无限拆分，设计不同的工序，最后还得汇总，这样的表结构如何设计

遇到一个表拆分问题，无限拆分，设计不同的工序，最后还得汇总，这样的表结构如何设计

为什么不用无限级分类表结构呢？加一个 parent 之类的字段表示父级，统计的时候直接使用 cte 根据父级生成 pth 不更方便么，另外，你的数据库是什么数据库，也有比较大的区分了，我用 mssql 给你出了个示例，不知道你的具体需求，随便弄了一个

with t(批号,一级ID,二级ID,三级ID,四级ID,五级ID,数量,用时) as (
    select convert(varchar(100),'phid'),CONVERT(varchar(100),null),CONVERT(varchar(100),null),CONVERT(varchar(100),null),CONVERT(varchar(100),null),CONVERT(varchar(100),null),40,107
    union all select 'phid','phid_a',null,null,null,null,30,0
    union all select 'phid','phid_b',null,null,null,null,10,0
    union all select 'phid','phid_a','phid_a_a',null,null,null,15,0
    union all select 'phid','phid_a','phid_a_b',null,null,null,15,0
    union all select 'phid','phid_b','phid_b_a',null,null,null,5,15
    union all select 'phid','phid_b','phid_b_b',null,null,null,5,12
    union all select 'phid','phid_a','phid_a_a','phid_a_a_a',null,null,5,13
    union all select 'phid','phid_a','phid_a_a','phid_a_a_b',null,null,10,0
    union all select 'phid','phid_a','phid_a_b','phid_a_b_a',null,null,5,16
    union all select 'phid','phid_a','phid_a_b','phid_a_b_b',null,null,10,0
    union all select 'phid','phid_a','phid_a_a','phid_a_a_b','phid_a_a_b_a',null,5,13
    union all select 'phid','phid_a','phid_a_a','phid_a_a_b','phid_a_a_b_b',null,5,12
    union all select 'phid','phid_a','phid_a_b','phid_a_b_b','phid_a_b_b_a',null,5,15
    union all select 'phid','phid_a','phid_a_b','phid_a_b_b','phid_a_b_b_b',null,5,11
),t1 as (
    select col,val,数量,用时,(case when CHARINDEX('_',val)>0 then SUBSTRING(val,1,LEN(val)-charindex('_',reverse(val))) else null end) as parent 
    from (
        select *,ROW_NUMBER() over(partition by val order by rid) as nid 
        from (
            select *,ROW_NUMBER() over(order by @@rowcount) as rid 
            from t
        ) a
        unpivot(val for col in (批号,一级ID,二级ID,三级ID,四级ID,五级ID)) p
    ) a
    where nid=1
)
select * 
from t1 a
outer apply (
    select isnull(SUM(用时),0) as 汇总用时
    from t1
    where CHARINDEX(a.val,val)=1 and val<>a.val
) b

这不就是颗树？