扁平化父级子级与多个父级的子级 [英] Flatten parent child hierarchy with multiple parents

问题描述

我的源结构中有一个父子层次结构，其中一个孩子可以指向他的父母，而他的父母可以存在多行.如果我们不扁平化层次结构，则意味着每个子行都必须存在于其父行之下.

I have a parent-child hierarchy in my source structure where a child could point to his parent and for his parent could exist multiple rows. If we than flatten the hierarchy this would mean that every child row need to exist beneath his parent row.

下面的图片可以澄清

由于我的源集是30万行，因此我已经下定决心如何在T-SQL中以高效的方式解决此问题，这将导致繁重的处理.非常感谢您的帮助！

I have already picked out my brains how to resolve this in a performant manner in T-SQL because my source set is 300K rows and this will lead to heavy processing. Help is greatly appreciated!

直接从上面的示例开始的代码，请参见下文.

Code to directly start from the example above see below.

SELECT *
INTO Orders
FROM (SELECT CAST('A' AS NVARCHAR(1)) AS Id, CAST('' AS NVARCHAR(1)) AS ParentId, CAST(10 AS int) AS Qty UNION ALL
SELECT CAST('A' AS NVARCHAR(1)) AS Id, CAST('' AS NVARCHAR(1)) AS ParentId, CAST(20 AS int) AS Qty UNION ALL
SELECT CAST('B' AS NVARCHAR(1)) AS Id, CAST('A' AS NVARCHAR(1)) AS ParentId, CAST(30 AS int) AS Qty UNION ALL
SELECT CAST('B' AS NVARCHAR(1)) AS Id, CAST('A' AS NVARCHAR(1)) AS ParentId, CAST(40 AS int) AS Qty UNION ALL
SELECT CAST('C' AS NVARCHAR(1)) AS Id, CAST('B' AS NVARCHAR(1)) AS ParentId, CAST(50 AS int) AS Qty UNION ALL
SELECT CAST('C' AS NVARCHAR(1)) AS Id, CAST('B' AS NVARCHAR(1)) AS ParentId, CAST(60 AS int) AS Qty UNION ALL
SELECT CAST('C' AS NVARCHAR(1)) AS Id, CAST('B' AS NVARCHAR(1)) AS ParentId, CAST(70 AS int) AS Qty) AS T1

推荐答案

我不知道 为什么 ，但这就是我要这样做的方式它...

I have no idea why you would want to do this, but this is how I would do it...

SELECT
  L1.ID,
  L2.ID,
  L3.ID,
  COALESCE(L3.Qty, L2.Qty, L1.Qty)
FROM
  yourTable   AS L1
OUTER APPLY
(
  SELECT * FROM yourTable WHERE parentID = L1.ID
  UNION ALL
  SELECT NULL, NULL, NULL
)
  AS L2
OUTER APPLY
(
  SELECT * FROM yourTable WHERE parentID = L2.ID
  UNION ALL
  SELECT NULL, NULL, NULL
)
  AS L3
WHERE
  L1.ParentID IS NULL

尽管如此，您将获得 很多 行.仅在您的示例中，8个输入行就变成18个输出行. 30万个输入行很容易变成数百万个输出行，具体取决于数据中的重复级别.

You're going to get a LOT of rows though. Just in your example 8 input rows became 18 output rows. 300k input rows could easily become many millions of output rows, depending on the level of duplication in the data.

这篇关于扁平化父级子级与多个父级的子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！