CTE是否用于计算辅助表中引用的树id

Question

我有一个简单的树表，其中我引用类型，子类型(无水平限制)，品牌和模型为一个简单的库存应用程序。

数据结构如下所示：(nodeIsModel是一个布尔字段，用于计算)

ID    parentID    label               nodeIsModel
 1     -1          Root                   0  
 2     1           IT                     0
 3     2           Desktops               0
 4     3           Hewlett-Packard        0 
 5     4           HP Z240                1
 6     4           H97M-PLUS              1
 7     2           Laptops                0
 8     7           DELL                   0
 9     8           G3 3579                1

诸若此类。

这个表(显示在德尔福下的DBTreeView中)给我提供了如下内容：

ID     Label
1      Root
2        |_IT
3        |__Desktops
4        |___Hewlett-Packard
5        |____HP Z240
6        |____H97M-PLUS
7        |__Laptops
8        |___DELL
9        |____G3 3579

此表是一个参考表，用户将在该参考表中挑选和分配设备、一组类型、子类型、品牌和型号。

设备保存在另一个表(data_items)中，并通过所选模型的id (树结构的最低级别)链接到上一个表。

例如，我的桌面计算机将保存为：

itemID    itemLabel         typeID
  1       RSI-HP-DESK-01      5

因为它是一个HP Z240工作站，它被引用为树表中的ID 5。

从这个typeID开始，我有一个CTE查询，它爬上树找到所有来自子ID的父母，这是没有问题的。

我的问题如下：

在显示DBTreeView时，我希望有一个列显示：

• 所有型号的

的项目计数

但同时也

每个品牌的sub-types

• the模型之和(包括模型)

• ，模型之和为每个模型E 218每种类型.H 219F 220

等等..。

例如，如果数据库中有2台HP Z240工作站和1台戴尔G3膝上型计算机，那么TreeView看起来应该是：

ID     Label                    NB
1      Root
2        |_IT                   3
3        |__Desktops            2
4        |___Hewlett-Packard    2
5        |____HP Z240           1
6        |____H97M-PLUS         1
7        |__Laptops             1
8        |___DELL               1
9        |____G3 3579           1

基于树表中的"nodeIsModel“布尔字段，我尝试了一些查询，有些查询没有查询，但在联接和子查询上我感到困惑，也得到了关于以下方面的错误消息：

在CTE中不允许

聚合查询

如有任何建议，将不胜感激。

根据Dale K的请求，下面是递归查询，它为我提供了从子ID开始的所有父级：

这个查询可以工作。我正在寻找一个新的查询，该查询将使用item表连接树表，并对分配给树每个级别的项的模型进行计数。

WITH CTE AS (
SELECT
    ID,
    parentID,
    label,
    CAST (nodeLevel AS INTEGER) AS LEVEL
FROM
    dico_TBM
WHERE
    ID = :lookupID
UNION ALL
    SELECT
        r.ID,
        t.parentID,
        t.label,
        LEVEL - 1
    FROM
        dico_TBM t
    INNER JOIN CTE r ON t.ID = r.parentID
) SELECT DISTINCT
    r.ID,
    r.parentID,
    r.label,
    LEVEL
FROM
    CTE r
WHERE
    LEVEL > 0
ORDER BY
    LEVEL

(nodeLevel是一个整数，表示我在问题中没有提到的树中节点的级别，认为它没有兴趣，并且:lookupID是我作为参数传递给查询的起始子节点的ID )

Answer 1

这里有一个方法可以帮助你解决这个问题。下面是我类似的设置

​

下面的查询给出了资源树中每个级别上的资源计数。

;with CTE1 AS (
  SELECT r.id, r.label, r.parentID, count(*) AS ItemCount 
    FROM resourcetree r 
    JOIN items i ON (r.id = i.resourceID) 
   GROUP BY r.id, r.label, r.parentID
), 
CTE2 AS (
  SELECT r.id, r.label, r.parentID, SUM(cte1.itemCount) AS ItemCount, 0 AS sumFlag 
    FROM resourceTree r 
    JOIN CTE1 ON (r.id = cte1.parentID) 
   GROUP BY r.id, r.label, r.parentID
  UNION ALL
  SELECT r.id, r.label, r.parentID, cte2.ItemCount AS ItemCount, 1 AS sumFlag 
    FROM resourceTree r 
    JOIN cte2 ON (r.id = cte2.parentID)
)
SELECT r.id, 
       r.label, 
       COALESCE(c2_1.ItemCount, c2_0.ItemCount, cte1.ItemCount, 0) AS ItemCount 
  FROM resourceTree r 
  LEFT JOIN cte1 ON r.id = cte1.id 
  LEFT JOIN cte2 c2_0 ON (r.id = c2_0.id AND c2_0.sumFlag = 0)
  LEFT JOIN (SELECT id, label, SUM(ItemCount) AS ItemCount 
               FROM CTE2 
              WHERE sumFlag = 1 
              GROUP BY id, label) c2_1 ON r.id = c2_1.id

下面是我得到的结果。希望能帮上忙。谢谢。

​

​

PS:我不需要使用'NodeIsModel‘标志

Answer 2

使用nodeIsModel = 1作为recursive cte的锚成员，从那里开始并上升到级别

with
rcte as
(
    select  *
    from    dico_TBM
    where   nodeIsModel = 1

    union all

    select  d.*
    from    rcte r
            inner join dico_TBM d   on  r.parnetID  = d.ID
)
select  ID, label, count(*) as NB
from    rcte
group by ID, label
order by ID