时间戳

首页 > 解决方案 > 按 GrandParent 计数子级和分区，位于未透视的层次结构中

sql - 按 GrandParent 计数子级和分区，位于未透视的层次结构中

问题描述

我想在每一行上显示 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 又引用了层次结构中更高的父级。'GrandParents' 是具有 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，但会更少，但现在计数已经混乱了。

标签： sqlsql-serversql-server-2012

解决方案

我希望这是最终结果：

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

推荐阅读