Свертка иерархического запроса Свертка

У меня есть следующая таблица:

parent_id   child_id    child_class
1   2   1
1   3   1
1   4   2
2   5   2
2   6   2

Parent_id представляет идентификатор папки. Идентификатор дочернего элемента представляет либо дочернюю папку (где дочерний_класс=1), либо дочерний файл (где дочерний_класс=2).

Я хотел бы получить счетчик свертки (снизу вверх) только для всех файлов (child_class=2) следующим образом. например, если C является конечной папкой (без дочерних папок) с 5 файлами, а B является родительской папкой C, в которой есть 4 файла, счетчик C должен показывать 5, а счетчик B должен показывать 9 (= 5 из C плюс 4 файла в B) и т. д. рекурсивно снизу вверх с учетом родственных папок и т. д.

В приведенном выше примере я ожидаю результатов ниже (уведомление 3 — это дочерняя папка без файлов в ней):

parent_id   FilesCounter
3   0
2   2
1   3

Я предпочитаю SQL-запрос для производительности, но функция также возможна.

Я пытался смешивать иерархический запрос с накопительным пакетом (sql 2008 r2) пока безуспешно.

Пожалуйста, порекомендуйте.


rollup tsql hierarchical
person user2965499    schedule 27.04.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Этот CTE должен помочь... Вот SQLFiddle.

SELECT parent_id, child_id, child_class,
(SELECT COUNT(*) FROM tbl a WHERE a.parent_id = e.parent_id AND child_class <> 1) AS child_count
INTO tbl2
FROM tbl e

;WITH CTE (parent_id, child_id, child_class, child_count)
AS
(
-- Start with leaf nodes
   SELECT parent_id, child_id, child_class, child_count 
   FROM tbl2
   WHERE child_id NOT IN (SELECT parent_id from tbl)
   UNION ALL
-- Recursively go up the chain
   SELECT e.parent_id, e.child_id, e.child_class, e.child_count + d.child_count
   FROM tbl2 e
   INNER JOIN CTE AS d
   ON e.child_id = d.parent_id
)
-- Statement that executes the CTE
SELECT FOLDERS.parent_id, max(ISNULL(child_count,0)) FilesCounter
FROM (SELECT parent_id FROM tbl2 WHERE parent_id NOT IN (select child_id from tbl2)
     UNION
     SELECT child_id FROM tbl2 WHERE child_class = 1) FOLDERS
LEFT JOIN CTE ON FOLDERS.parent_id = CTE.parent_id
GROUP BY FOLDERS.parent_id
person Zak    schedule 28.04.2014

arrow_upward
0
arrow_downward

Ответ Зака ​​был близок, но корневая папка не свернута. Работа выполняется следующим образом:

with par_child as (
select 1 as parent_id,             2 as child_id,              1 as child_class
union all select 1,              3,              1
union all select 1,              4,              2
union all select 2,              5,              1
union all select 2,              6,              2
union all select 2,              10,           2  
union all select 3,              11,           2  
union all select 3,              7 ,             2
union all select 5,              8 ,             2
union all select 5,              9 ,             2
union all select 5,              12,           1  
union all select 5,              13,           1  
)
, child_cnt as 
(
      select parent_id as root_parent_id, parent_id, child_id, child_class, 1 as lvl from par_child    union all
      select cc.root_parent_id, pc.parent_id, pc.child_id, pc.child_class, cc.lvl + 1 as lvl from
      par_child pc join child_cnt cc on (pc.parent_id=cc.child_id)
),
distinct_folders as (
select distinct child_id as folder_id from par_child where child_class=1
)
select root_parent_id, count(child_id) as cnt from child_cnt where child_class=2 group by root_parent_id
union all
select folder_id, 0 from distinct_folders df where not exists (select 1 from par_child pc where df.folder_id=pc.parent_id)
person user2965499    schedule 29.04.2014