主页 / dba / 问题 / 138928
Accepted
markokstate
Asked: 2016-05-19 12:39:46 +0800 CST

递归 CTE 为所有孩子找到总计

  • 772

这是我想使用递归T-SQL查询（大概CTE）搜索的程序集树，预期结果如下。我想知道给定任何零件的每个组件的总量。

这意味着如果我搜索“铆钉”，我想知道程序集中每个级别的总计数，而不仅仅是直接子级计数。

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

目前，我可以获得直接父母，但想知道如何扩展我的 CTE 以允许我向上滚动此信息。

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i 
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

创建脚本

CREATE TABLE [dbo].[Instances] ( 
  [InstanceID] NVARCHAR (50) NOT NULL, 
  [Part] NVARCHAR (50) NOT NULL, 
  [ParentID] NVARCHAR (50) NOT NULL, );



INSERT INTO Instances 
Values 
  (1, 'Assembly', 0), 
  (50, 'Rivet', 1), 
  (50, 'Rivet', 1), 
  (2, 'SubAssembly', 1), 
  (50, 'Rivet', 2), 
  (51, 'Bolt', 2), 
  (51, 'Bolt', 2), 
  (3, 'SubSubAssembly', 2), 
  (50, 'Rivet', 3), 
  (50, 'Rivet', 3), 
  (4, 'SubAssembly2', 1), 
  (50, 'Rivet', 4), 
  (51, 'Bolt', 4)
sql-server t-sql

2 个回答

  1. Best Answer

    此递归 CTE ( SQL Fiddle ) 应该适用于您的示例：

    WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*) 
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

    输出

    ParentID    Count
1           6
2           3
3           2
4           1

    注意：您在评论中提到该问题仅包含简化的示例表，真实数据具有适当的索引并充分处理重复和数据。

    使用的数据（SQL Fiddle）：

    DECLARE @Instances TABLE( 
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES 
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;
    • 17

  2. 我不确定我理解您所说的“金额”是什么意思，以及您的示例中的表（？）PartInstances和列id和计数来自哪里，但我从您的示例数据中计算了我的猜测。

    ;with ins as (
select [InstanceID], [Part],[ParentID],0 lvl
from instances where ParentID=0
union all
select i.[InstanceID], i.[Part],i.[ParentID], lvl+1
from instances i 
inner join ins on i.parentid=ins.InstanceID
)
select InstanceID,part,COUNT(*) cnt
from ins
group by instanceid,part

    我希望这会给你一些想法。

    更新

    我知道这是一个测试示例，但是您的数据从1NF. 很可能您的表应该分成两部分并标准化。

    • -1

相关问题