Sql 计算分段的总权重

我正在try 使用递归计算SQL Server 2022中的段和子段的总权重，我甚至还没有接近令人满意的结果，也许您可以帮助我调整递归CTE或使用其他方法.

我有这两张桌子--segment和segmentpart-- struct 是这样的:

CREATE TABLE SEGMENT 
(
    SEGMENTID INT NOT NULL,
    SEGMENTID_FK INT,
    SEGMENTPARTSID INT NOT NULL,
    PRIMARY KEY (SEGMENTID)
);

CREATE TABLE SEGMENTPART 
(
    SEGMENTPARTSID INT NOT NULL,
    SEGMENTTYPEID INT NOT NULL,
    NAME VARCHAR(200) NOT NULL,
    SERIALNUMBER VARCHAR(150),
    WEIGHT DECIMAL(14,4),
    PRIMARY KEY (SEGMENTPARTSID)
);

INSERT INTO SEGMENT VALUES (1, NULL, 1);
INSERT INTO SEGMENT VALUES (2, 1, 2);
INSERT INTO SEGMENT VALUES (3, 1, 3);
INSERT INTO SEGMENT VALUES (4, 2, 4);
INSERT INTO SEGMENT VALUES (5, 2, 5);
INSERT INTO SEGMENT VALUES (6, 3, 6);

INSERT INTO SEGMENTPART VALUES (1, 101, 'Component 1', 'SN001', 10.5);
INSERT INTO SEGMENTPART VALUES (2, 102, 'Component 2', 'SN002', 15.2);
INSERT INTO SEGMENTPART VALUES (3, 103, 'Component 3', 'SN003', 20.3);
INSERT INTO SEGMENTPART VALUES (4, 104, 'Component 4', 'SN004', 8.7);
INSERT INTO SEGMENTPART VALUES (5, 105, 'Component 5', 'SN005', 12.8);
INSERT INTO SEGMENTPART VALUES (6, 106, 'Component 6', 'SN006', 40.8);

这应该是正确的输出:

SEGMENTID  Total_weight
-----------------------
1             108.3
2              36.7
3              61.1
4               8.7
5              12.8
6              40.8

因此，对于Segentid=1，我需要他的总和以及他的所有子段-2、3、4、5、6，所以它将=108.3

对于Segentid=2，我需要他和他的子段(在本例中为4和5个子段)的总和，因此总和将是36.7，依此类推.

我试过的就是这样，但我不知道要做些什么才能使它起作用:

WITH RCTE AS 
(
    SELECT s1.SEGMENTID, sp.WEIGHT, s1.SEGMENTID_FK
    FROM SEGMENT s1
    INNER JOIN SEGMENTPART sp ON s1.SEGMENTPARTSID = sp.SEGMENTPARTSID
    WHERE s1.SEGMENTID_FK IS NULL

    UNION ALL

    SELECT s2.SEGMENTID, sp.WEIGHT,  s2.SEGMENTID_FK
    FROM SEGMENT s2
    INNER JOIN SEGMENTPART sp ON  s2.SEGMENTPARTSID = sp.SEGMENTPARTSID
    INNER JOIN RCTE ON s2.SEGMENTID_FK = RCTE.SEGMENTID
)
SELECT * FROM rcte

结果是这样的，这是完全错误的:

SEGMENTID     WEIGHT    SEGMENTID_FK
------------------------------------
1             10.5000   NULL
2             15.2000   1
3             20.3000   1
6             40.8000   3
4              8.7000   2
5             12.8000   2

如果您需要额外的信息或有什么不清楚的，请让我知道.

提前谢谢你了