SQL Server为每个节点重复第一行，并为层次结构节点获取相同的行号

Question

我有多个嵌套的cte，它们返回这个结果

WITH X AS (    
SELECT BATCH, CHILD_BATCH, 0 AS LVL, 
N'/' + CONVERT(NVARCHAR(4000),BATCH) + N'/' + CONVERT(NVARCHAR(4000),CHILD_BATCH) + N'/' AS HIERARCHY
FROM MYTABLE
WHERE BATCH = @LOTTO_INIZIALE  
UNION ALL
SELECT T.BATCH, T.CHILD_BATCH, X.LVL + 1 AS LVL, X.HIERARCHY + CONVERT(NVARCHAR(4000), L.CHILD_BATCH) + N'/'
FROM MYTABLE T INNER JOIN X ON T.BATCH = X.CHILD_BATCH  
)
SELECT * FROM X
╔════════╦═════════════╦═════════════════════════════════════════════╦═════╗
║ BATCH  ║ CHILD_BATCH ║                  HIERARCHY                  ║ LVL ║
╠════════╬═════════════╬═════════════════════════════════════════════╬═════╣
║ NZ1677 ║ NZ1671      ║ /NZ1677/NZ1671/                             ║   0 ║
║ NZ1671 ║ NZ1646      ║ /NZ1677/NZ1671/NZ1646/                      ║   1 ║
║ NZ1646 ║ NZ1125      ║ /NZ1677/NZ1671/NZ1646/NZ1125/               ║   2 ║
║ NZ1125 ║ NZ0960      ║ /NZ1677/NZ1671/NZ1646/NZ1125/NZ0960/        ║   3 ║
║ NZ0960 ║ NY2443      ║ /NZ1677/NZ1671/NZ1646/NZ1125/NZ0960/NY2443/ ║   4 ║
║ NZ1677 ║ NZ1672      ║ /NZ1677/NZ1672/                             ║   0 ║
║ NZ1672 ║ NZ1646      ║ /NZ1677/NZ1672/NZ1646/                      ║   1 ║
║ NZ1646 ║ NZ1125      ║ /NZ1677/NZ1672/NZ1646/NZ1125/               ║   2 ║
║ NZ1125 ║ NZ0960      ║ /NZ1677/NZ1672/NZ1646/NZ1125/NZ0960/        ║   3 ║
║ NZ0960 ║ NY2443      ║ /NZ1677/NZ1672/NZ1646/NZ1125/NZ0960/NY2443/ ║   4 ║
║ NZ1672 ║ NZ1647      ║ /NZ1677/NZ1672/NZ1647/                      ║   1 ║
║ NZ1647 ║ NZ1444      ║ /NZ1677/NZ1672/NZ1647/NZ1444/               ║   2 ║
║ NZ1444 ║ NZ1442      ║ /NZ1677/NZ1672/NZ1647/NZ1444/NZ1442/        ║   3 ║
║ NZ1442 ║ NY2443      ║ /NZ1677/NZ1672/NZ1647/NZ1444/NZ1442/NY2443/ ║   4 ║
╚════════╩═════════════╩═════════════════════════════════════════════╩═════╝

我希望为同一层次结构节点获得一个具有相同值的新列，如下所示：

╔════════╦═════════════╦═════════════════════════════════════════════╦═════╦═════╗
║ BATCH  ║ CHILD_BATCH ║                  HIERARCHY                  ║ LVL ║ IX  ║
╠════════╬═════════════╬═════════════════════════════════════════════╬═════╬═════╣
║ NZ1677 ║ NZ1671      ║ /NZ1677/NZ1671/                             ║   0 ║   1 ║
║ NZ1671 ║ NZ1646      ║ /NZ1677/NZ1671/NZ1646/                      ║   1 ║   1 ║
║ NZ1646 ║ NZ1125      ║ /NZ1677/NZ1671/NZ1646/NZ1125/               ║   2 ║   1 ║
║ NZ1125 ║ NZ0960      ║ /NZ1677/NZ1671/NZ1646/NZ1125/NZ0960/        ║   3 ║   1 ║
║ NZ0960 ║ NY2443      ║ /NZ1677/NZ1671/NZ1646/NZ1125/NZ0960/NY2443/ ║   4 ║   1 ║
║ NZ1677 ║ NZ1672      ║ /NZ1677/NZ1672/                             ║   0 ║   2 ║
║ NZ1672 ║ NZ1646      ║ /NZ1677/NZ1672/NZ1646/                      ║   1 ║   2 ║
║ NZ1646 ║ NZ1125      ║ /NZ1677/NZ1672/NZ1646/NZ1125/               ║   2 ║   2 ║
║ NZ1125 ║ NZ0960      ║ /NZ1677/NZ1672/NZ1646/NZ1125/NZ0960/        ║   3 ║   2 ║
║ NZ0960 ║ NY2443      ║ /NZ1677/NZ1672/NZ1646/NZ1125/NZ0960/NY2443/ ║   4 ║   2 ║
║ NZ1672 ║ NZ1647      ║ /NZ1677/NZ1672/NZ1647/                      ║   1 ║   3 ║
║ NZ1647 ║ NZ1444      ║ /NZ1677/NZ1672/NZ1647/NZ1444/               ║   2 ║   3 ║
║ NZ1444 ║ NZ1442      ║ /NZ1677/NZ1672/NZ1647/NZ1444/NZ1442/        ║   3 ║   3 ║
║ NZ1442 ║ NY2443      ║ /NZ1677/NZ1672/NZ1647/NZ1444/NZ1442/NY2443/ ║   4 ║   3 ║
╚════════╩═════════════╩═════════════════════════════════════════════╩═════╩═════╝

有可能吗？

如果有连续的hierarchyd子节点，我也希望重复级别0。(例如/NZ1677/NZ1672/(

编辑

解释起来有点复杂，但我尽力了。

在我的表中，我有一个带有一个或多个孩子的批次列表。

对于每个层次结构级别，我可以有一个o加子级别。

在每一行中，我都有批次和子批次的数量。

例如，另一批可以是这样的(如果有帮助的话，我还添加了"prev_batch"列(：

+------------+--------+-------------+-----------+-----------+------------------------+-----+
| PREV_BATCH | BATCH  | CHILD_BATCH | BATCH_QTY | CHILD_QTY |       HIERARCHY        | LVL |
+------------+--------+-------------+-----------+-----------+------------------------+-----+
|            | NB0166 | NA1367M     |  119.3700 |    0.0450 | /NB0166/NA1367M/       |   0 |
|            | NB0166 | NA1938M     |  119.3700 |    0.0650 | /NB0166/NA1938M/       |   0 |
|            | NB0166 | NA3204S     |  119.3700 |    0.0270 | /NB0166/NA3204S/       |   0 |
|            | NB0166 | NB0019      |  119.3700 |   18.0400 | /NB0166/NB0019/        |   0 |
| NB0166     | NB0019 | NA3938      |  109.6700 |  100.0000 | /NB0166/NB0019/NA3938/ |   1 |
|            | NB0166 | NB0021      |  119.3700 |   91.9600 | /NB0166/NB0021/        |   0 |
| NB0166     | NB0021 | NA1924      |  109.7400 |   40.0000 | /NB0166/NB0021/NA1924/ |   1 |
| NB0166     | NB0021 | NA2251      |  109.7400 |   10.0000 | /NB0166/NB0021/NA2251/ |   1 |
| NB0166     | NB0021 | NA2616      |  109.7400 |   50.0000 | /NB0166/NB0021/NA2616/ |   1 |
+------------+--------+-------------+-----------+-----------+------------------------+-----+

我的想法是为每个层次结构节点添加具有相同值的IX列(就像我在上面解释的那样(。

我想使用lead函数来读取下一级批次的数量(我需要做一些计算(。

使用IX列，我可以正确地划分导线。

对完成这项任务的一些最佳想法持开放态度。

Answer 1

如果我理解正确，当前一行位于比当前行更深的级别时，您希望增加。

这可能是最简单的使用LAG()和累积总和：

WITH X AS (    
SELECT BATCH, CHILD_BATCH, 0 AS LVL, 
(N'/' + CONVERT(NVARCHAR(4000),BATCH) + N'/' + 
CONVERT(NVARCHAR(4000),CHILD_BATCH) + N'/'
) AS HIERARCHY
FROM MYTABLE
WHERE BATCH = @LOTTO_INIZIALE  
UNION ALL
SELECT T.BATCH, T.CHILD_BATCH, X.LVL + 1 AS LVL,
(X.HIERARCHY + CONVERT(NVARCHAR(4000), L.CHILD_BATCH) + N'/')
FROM MYTABLE T INNER JOIN
X
ON T.BATCH = X.CHILD_BATCH  
)
SELECT X.*,
SUM(CASE WHEN prev_lvl < lvl THEN 0 ELSE 1 END) OVER (ORDER BY HIERARCHY) as IX
FROM (SELECT X.*,
LAG(LVL) OVER (ORDER BY HIERARCHY) as prev_lvl
FROM X
) X;

Answer 2

在您的情况下，这可能无关紧要，但我建议记录有一个"父"；列而不是"列"；"孩子"；柱这是因为在许多情况下，当您插入具有父/子关系的记录时；"父"；已经存在，而子项可能要到以后才能插入。这意味着，当您插入记录时，您手头有所需的所有信息，而不必在插入子记录后立即更新行。此外，子记录通常只有一个父记录，但父记录可以有任意数量的子记录。您还可以更容易地强制执行引用完整性。

不管怎样，你的问题。

我已将以下解释为您需要一个"；0"；记录每个"；组"；。这就是为什么存在额外的"；0"；记录在我的输出中，但不在您的输出中。

如果有几个层次结构子节点，我也希望重复级别0。

假设IX只是在"；孙"；，我想知道是否需要在任何可用的子代级别上进行分组，所以我将其参数化了。我在数据中没有足够的方差来测试这一点，但看起来它应该有效。

DECLARE @MYTABLE TABLE
(   BATCH CHAR(6),
CHILD_BATCH CHAR(6)
)
DECLARE @LOTTO_INIZIALE CHAR(6) = 'NZ1677'
DECLARE @GROUPING_LEVEL INT     = 1 -- 1 = grandchild, 2 = great-grandchild... n = nth descendant +2 (given our base is child)
INSERT INTO @MYTABLE
VALUES ('NZ1677', 'NZ1671'),
('NZ1671', 'NZ1646'),
('NZ1646', 'NZ1125'),
('NZ1125', 'NZ0960'),
('NZ0960', 'NY2443'),
('NZ1677', 'NZ1672'),
('NZ1672', 'NZ1646'),
('NZ1646', 'NZ1125'),
('NZ1125', 'NZ0960'),
('NZ0960', 'NY2443'),
('NZ1672', 'NZ1647'),
('NZ1647', 'NZ1444'),
('NZ1444', 'NZ1442'),
('NZ1442', 'NY2443')
;WITH cte_batch_hierarchy
AS (SELECT BATCH, 
CHILD_BATCH,
GRP              = CONVERT(VARCHAR(MAX), '/') + BATCH + '/' + CHILD_BATCH + '/',
HIERARCHY        = CONVERT(VARCHAR(MAX), '/') + BATCH + '/' + CHILD_BATCH + '/',
LVL              = 0
FROM @MYTABLE  t
WHERE t.BATCH = @LOTTO_INIZIALE 
UNION ALL
SELECT mt.BATCH, 
mt.CHILD_BATCH,
GRP = CASE WHEN bh.LVL < @GROUPING_LEVEL THEN HIERARCHY + mt.CHILD_BATCH  + '/' ELSE GRP END,
HIERARCHY = HIERARCHY + mt.CHILD_BATCH  + '/',
LVL       = LVL + 1
FROM cte_batch_hierarchy bh
JOIN @MYTABLE mt ON bh.CHILD_BATCH = mt.BATCH
)
SELECT bh.BATCH, 
bh.CHILD_BATCH, 
bh.HIERARCHY, 
bh.LVL, 
IX = DENSE_RANK() OVER(ORDER BY ISNULL(grp.GRP, bh.GRP)) 
FROM cte_batch_hierarchy bh
LEFT JOIN (SELECT DISTINCT GRP 
FROM cte_batch_hierarchy 
WHERE LVL > @GROUPING_LEVEL) grp ON grp.GRP LIKE bh.HIERARCHY + '%'
GROUP BY
bh.BATCH, 
bh.CHILD_BATCH, 
bh.HIERARCHY, 
bh.LVL,
ISNULL(grp.GRP, bh.GRP)
ORDER BY 
DENSE_RANK() OVER(ORDER BY ISNULL(grp.GRP, bh.GRP)),
LVL