时间戳

首页 > 解决方案 > 基于汇总类型的树结构中的总分数

sql-server - 基于汇总类型的树结构中的总分数

问题描述

我正在尝试计算树结构的每个节点的总数，计算总数取决于汇总类型

我有如下结构

树形结构

假设我的起点是 B1 ：所以总分 = (20 +40 +110 +60 +90)

假设我的起点 C2：分数是 110

我设法得到了所有的孩子，但是当涉及到汇总时，我被卡住了，请帮忙

示例数据脚本

+--------+----------+------+------------+-------------------+------------------+
| Teamid | ParentId | Name | ParentName |       Type        | Key Result Value |
+--------+----------+------+------------+-------------------+------------------+
|      1 | null     | A1   | Null       | Current and Child |               10 |
|      2 | 1        | B1   | A1         | Current and Child |               20 |
|      3 | 1        | B2   | A1         | Current and Child |               30 |
|      4 | 2        | C1   | B1         | Current List      |               40 |
|      5 | 2        | C2   | B1         | Child List        |               50 |
|      6 | 2        | C3   | B1         | Current and Child |               60 |
|      7 | 4        | D1   | C1         | Current and Child |               70 |
|      8 | 5        | D2   | C2         | Child List        |               80 |
|      9 | 6        | D3   | C3         | Current and Child |               90 |
|     10 | 7        | E1   | D1         | Current List      |              100 |
|     11 | 8        | E2   | D2         | Current and Child |              110 |
+--------+----------+------+------------+-------------------+------------------+

树表脚本：

CREATE TABLE TreeTable(
   Teamid           INTEGER  NOT NULL PRIMARY KEY 
  ,ParentId         INTEGER 
  ,Name             VARCHAR(2) NOT NULL
  ,ParentName       VARCHAR(4)
  ,Type             VARCHAR(17) NOT NULL
  ,Key_Result_Value INTEGER  NOT NULL
);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (1,NULL,'A1',NULL,'Current and Child',10);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (2,1,'B1','A1','Current and Child',20);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (3,1,'B2','A1','Current and Child',30);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (4,2,'C1','B1','Current List',40);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (5,2,'C2','B1','Child List',50);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (6,2,'C3','B1','Current and Child',60);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (7,4,'D1','C1','Current and Child',70);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (8,5,'D2','C2','Child List',80);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (9,6,'D3','C2','Current and Child',90);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (10,7,'E1','D1','Current List',100);
INSERT INTO TreeTable(Teamid,ParentId,Name,ParentName,Type,Key_Result_Value) VALUES (11,8,'E2','E2','Current and Child',110);

我的尝试：

WITH
CTE_Groups
AS
(
    SELECT
        TreeTable.teamid AS StartID
        ,TreeTable.Name AS StartName
        ,TreeTable.teamid
        ,TreeTable.ParentID
        ,TreeTable.Name
        ,1 AS Lvl
        ,TreeTable.Type
        ,TreeTable.Key_Result_Value
    FROM TreeTable
    
    UNION ALL

    SELECT
        CTE_Groups.StartID
        ,CTE_Groups.StartName
        ,TreeTable.teamid
        ,TreeTable.ParentID
        ,TreeTable.Name
        ,CTE_Groups.Lvl + 1 AS Lvl
        ,TreeTable.Type
        ,TreeTable.Key_Result_Value

    FROM
        TreeTable
        INNER JOIN CTE_Groups ON CTE_Groups.teamid = TreeTable.ParentID
)
select * from CTE_Groups
order by startid,lvl

在此处输入图像描述

请帮助 在此处输入图像描述 预期结果：

+--------+-----------------------+
| TeamID |         Score         |
+--------+-----------------------+
| E1     | 100                   |
| D1     | 70 + 100              |
| C1     | 40                    |
| E2     | 110                   |
| D2     | 110                   |
| C2     | 110                   |
| D3     | 90                    |
| C3     | 60+90                 |
| B1     | 20+40+110+60+90       |
| B2     | 30                    |
| A1     | 10+30+20+40+110+60+90 |
+--------+-----------------------+

标签： sql-serverssms

解决方案

使用递归列出子节点是一个好的开始。使用case表达式，您还可以跟踪组类型以及何时添加或忽略值（忽略 = 使用0而不是实际节点值）。

解决方案

with rcte as
(
  select t.Name as GroupName,
         t.Type as GroupType,
         t.Name,
         t.Type,
         case t.Type
           when 'Child list' then 0 -- if the current node must be ignored, then the current value changes to 0
           else t.Key_Result_Value
         end as Value
  from TreeTable t
union all
  select r.GroupName,
         r.GroupType,
         t.Name,
         case r.Type
           when 'Current list' then 'Current list' -- if the parent was the end of the sum, then it stays the end of the sum
           else t.Type
         end,
         case
           when r.Type = 'Current list' then 0 -- if the parent was the end of the sum, then the current value changes to 0
           when t.Type = 'Child list' then 0 -- if the current node must be ignored, then the current value changes to 0
           else t.Key_Result_Value
         end
  from rcte r
  join TreeTable t
    on t.ParentName = r.Name
)
select r.GroupName,
       sum(r.Value) as GroupSum,
       string_agg(case when r.Value <> 0 then convert(nvarchar(3), r.Value) end, '+') as GroupSumFormula
from rcte r
group by r.GroupName
order by r.GroupName;

结果

GroupName  GroupSum  GroupSumFormula
---------  --------  ---------------------
A1         360       10+20+30+40+60+90+110
B1         320       20+40+60+90+110
B2         30        30
C1         40        40
C2         110       110
C3         150       60+90
D1         170       100+70
D2         110       110
D3         90        90
E1         100       100
E2         110       110

小提琴以查看实际情况。

推荐阅读