按祖父级计算子项和分区，该项驻留在未透视的层次结构中

Question

我想在每一行上显示 MainGroundID 中有多少个 BuildingID，同时必须处理未透视的父子层次结构。

不幸的是，GroundID和MainGroundID的编写方式中没有逻辑(尽管在我的示例中看起来是这样，因为我制作了一个示例数据集(。

PMEBuilding 
BuildingID, GroundID
1,          100
2,          100
3,          101
4,          201
5,          201
6,          201
7,          202

实际上，上表有 34K 行和 80+ 字段。

上表中的 GroundID 通过 GroundID 是 N：1 到下表。 在 PMEGroudn 表中，一些 GroundID 指的是某个 MainGroundID，而 MainGroundID 又指层次结构中更高的父项。"祖父母"是那些将 NULL 值作为 GroundID 的祖父母。

PMEGround   
GroundID,    MainGroundID
1,           NULL --GrandParent
10,          1
100,         10
101,         10
2,           NULL --GrandParent
20,          2
201,         20
202,         20

实际上，上表有 2K 行，其中大约 500 个"祖父母"。

我希望这是最终结果：

MainGroundID    MainGroundBuildingCount
1,              3
2,              7

以下代码是我到目前为止使用的代码，但它还没有完全工作：

;WITH UNPIVOT_HIERARCHY AS (
SELECT GROUNDID
,MAINGROUNDID 
,PathID         = CAST(GROUNDID AS VARCHAR(MAX))
FROM    PMEGROUND
WHERE   NULLIF(MainGroundID, '') IS NULL
UNION   All
SELECT  GROUNDID    = r.GROUNDID
,MAINGROUNDID   = r.MAINGROUNDID 
,PathID         = p.PathID+CONCAT(',',CAST(r.GROUNDID AS VARCHAR(MAX)))
FROM   PMEGROUND r
JOIN   UNPIVOT_HIERARCHY p ON r.MAINGROUNDID  = p.GROUNDID
)
SELECT
B.Lvl3  AS 'MainGroundID' --This is the GrandParent, which works fine
,COUNT(PMEBUILDING.GROUNDID) OVER (PARTITION BY B.Lvl3) AS 'MainGroundCountBuildings'
FROM PMEGROUND
LEFT JOIN UNPIVOT_HIERARCHY
ON UNPIVOT_HIERARCHY.GROUNDID = PMEGROUND.GROUNDID
LEFT JOIN PMEBUILDING
ON PMEBUILDING.GROUNDID = PMEGROUND.GROUNDID
CROSS Apply (
SELECT Lvl1 = xDim.value('/x[3]','varchar(50)')
,Lvl2 = xDim.value('/x[2]','varchar(50)')
,Lvl3 = xDim.value('/x[1]','varchar(50)')
,Lvl4 = xDim.value('/x[4]','varchar(50)')
FROM  ( VALUES (CAST('<x>' + REPLACE(PathID,',','</x><x>')+'</x>' AS xml))) B(xDim)
) B
GROUP BY B.Lvl3, PMEBUILDING.GROUNDID

如果没有GROUP BY，它会给出重复的 MainGroundID，但正确的计数。 有了GROUP BY它仍然提供重复的 MainGroundID，但更少，但现在计数搞砸了。

Answer 1

我希望这是最终结果：

MainGroundID    MainGroundBuildingCount
1,              3
2,              7

你不是说最终结果应该是吗？

MainGroundID    MainGroundBuildingCount
1,              3
2,              4

假设，根据给定的数据，有 3 个层次结构级别，PMEBuilding.GroundID 仅包含孙子，我将使用以下方法来实现最终结果：

select 
gp.GroundID, count(distinct b.BuildingID)
from PMEGround gp
join PMEGround p on p.MainGroundID = gp.GRoundID
join PMEGround c on c.MainGroundID = p.GRoundID
join PMEBuilding b on b.GroundID = c.GroundID
where gp.MainGroundID is null
group by gp.GroundID
order by 1