分组分层数据(parentID+ID)并运行sum？

Question

我有以下数据：

ID    parentID    Text        Price
1                 Root
2     1           Flowers
3     1           Electro
4     2           Rose        10
5     2           Violet      5
6     4           Red Rose    12
7     3           Television  100
8     3           Radio       70
9     8           Webradio    90

我试图将这些数据与Reporting 2008进行分组，并将每组1级(花/电)和0级(根)的价格相加。

我在ID上有一个与parendID的递归父表分组的表，我能够计算级别0的和(仅在组外的表中多出一行)，但不知何故，我无法在每个级别上创建sum的每个组，因为SRSS会“创建”每个级别的组。我想要的结果如下：

ID    Text        Price
1     Root
|2    Flowers
|-4   Rose        10
|-5   Violet      5
| |-6 Red Rose    12
|     Group Sum-->27
|3    Electro
|-7   Television  100
|-8   Radio       70
  |-9 Webradio    90
      Group Sum-->260
----------------------
Total             287

(为级别澄清而添加的ID缩进)

用我目前的方法，我无法得到组和，所以我想我需要以下数据结构：

ID    parentID    Text        Price    level0    level1    level2    level3
1                 Root                 1
2     1           Flowers              1         1
3     1           Electro              1         2
4     2           Rose        10       1         1         1
5     2           Violet      5        1         1         2
6     4           Red Rose    12       1         1         1         1
7     3           Television  100      1         2         1
8     3           Radio       70       1         2         2
9     8           Webradio    90       1         2         2         1

当具有上述结构时，我可以创建level0的外部分组，并相应地创建子分组level1、level2、level3。当现在在level1上有一个“组和”，以及组外的总和，我完全有我想要的。

我的问题是：如何用当前的数据结构实现我想要的结果，或者如何转换当前的数据结构(左外联接？)临时进入“新数据结构”--这样我就可以在临时表上运行我的报告了？

谢谢你慢慢来，丹尼斯

Answer 1

我找到了一种很难看的方法来做我想做的事情--也许还有更好的方法？

SELECT A.Text, A.Price,                   
  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                CASE
                    WHEN B.Text IS NULL
                    THEN
                        A.ID
                    ELSE B.ID
                END
            ELSE C.ID
            END
    ELSE D.ID
  END
  AS LEV0,

  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                CASE
                    WHEN B.Text IS NULL
                    THEN
                        NULL
                    ELSE A.ID
                END
            ELSE B.ID
            END
    ELSE C.ID
  END
  AS LEV1,

  CASE
    WHEN D.Text IS NULL
    THEN
        CASE
            WHEN C.Text IS NULL
            THEN
                NULL
            ELSE A.ID
            END
    ELSE B.ID
  END
  AS LEV2,

  CASE
    WHEN D.Text IS NULL
    THEN NULL
    ELSE A.ID
  END
  AS LEV3

FROM         dbo.testOld AS A LEFT OUTER JOIN
                  dbo.testOld AS B ON A.parentID = B.ID LEFT OUTER JOIN
                  dbo.testOld AS C ON B.parentID = C.ID LEFT OUTER JOIN
                  dbo.testOld AS D ON C.parentID = D.ID

这方面的产出如下：

Text       Price       LEV0        LEV1        LEV2        LEV3
---------- ----------- ----------- ----------- ----------- -----------
Root       NULL        1           NULL        NULL        NULL
Flowers    NULL        1           3           NULL        NULL
Electro    NULL        1           4           NULL        NULL
Television 100         1           4           5           NULL
Radio      70          1           4           6           NULL
Rose       10          1           3           7           NULL
Violet     5           1           3           8           NULL
Webradio   90          1           4           5           14
Red Rose   12          1           3           7           15

有了这个结构，我可以继续在LEV0-3列上创建4个嵌套组，包括每个组的小计(如我想要的结果中所示)。

Answer 2

WITH    q AS
        (
        SELECT  id, parentId, price
        FROM    mytable
        UNION ALL
        SELECT  p.id, p.parentID, q.price
        FROM    q
        JOIN    mytable p
        ON      p.id = q.parentID
        )
SELECT  id, SUM(price)
FROM    q
GROUP BY
        id

更新：

要检查的测试脚本：

DECLARE @table TABLE (id INT NOT NULL PRIMARY KEY, parentID INT, txt VARCHAR(200) NOT NULL, price MONEY)

INSERT
INTO @table
SELECT 1, NULL, 'Root', NULL
UNION ALL
SELECT 2, 1, 'Flowers', NULL
UNION ALL
SELECT 3, 1, 'Electro', NULL
UNION ALL
SELECT 4, 2, 'Rose', 10
UNION ALL
SELECT 5, 2, 'Violet', 5
UNION ALL
SELECT 6, 4, 'Red Rose', 12
UNION ALL
SELECT 7, 3, 'Television', 100
UNION ALL
SELECT 8, 3, 'Radio', 70
UNION ALL
SELECT 9, 8, 'Webradio', 90;

WITH    q AS
        (
        SELECT  id, parentId, price
        FROM    @table
        UNION ALL
        SELECT  p.id, p.parentID, q.price
        FROM    q
        JOIN    @table p
        ON      p.id = q.parentID
        )
SELECT  t.*, psum
FROM    (        
        SELECT  id, SUM(price) AS psum
        FROM    q
        GROUP BY
                id
        ) qo
JOIN    @table t
ON      t.id = qo.id

结果如下：

1       NULL    Root            NULL    287,00
2       1       Flowers         NULL    27,00
3       1       Electro         NULL    260,00
4       2       Rose            10,00   22,00
5       2       Violet          5,00    5,00
6       4       Red Rose        12,00   12,00
7       3       Television      100,00  100,00
8       3       Radio           70,00   160,00
9       8       Webradio        90,00   90,00