Группировка иерархических данных (parentID + ID) и текущая сумма?

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

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 Services 2008 и получить сумму цен на группу для уровня 1 (Цветы / Электро) и для уровня 0 (Корень).

У меня есть таблица, сгруппированная по [ID] с рекурсивным родительским элементом [parendID], и я могу вычислить сумму для уровня 0 (еще одна строка в таблице вне группы), но почему-то я не могу создать сумма на группу, поскольку 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    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 соответственно. Имея "групповую сумму" на уровне 1 и общую сумму вне группы, я имею ТОЧНО то, что хочу.

У меня следующий вопрос: Как мне либо достичь желаемого результата с моей текущей структурой данных, либо как временно преобразовать мою текущую структуру данных (внешние левые соединения?) в «новую структуру данных» - чтобы я мог запустить свой отчет из временной таблицы?

Спасибо, что нашли время, Деннис


sql reporting-services ssrs-2008
person Dennis G    schedule 04.02.2010    source источник
comment
Есть ли ограничение на количество возможных рекурсий? Вы упомянули 4 уровня в своем примере. Если он ограничен, вы можете присоединиться вслепую и влево 4 раза, иначе вам, вероятно, придется создать функцию или добавить столбец LevelNumber.   -  person Alex Bagnolini    schedule 04.02.2010
comment
Максимум 4 уровня, я бы сказал, что   -  person Dennis G    schedule 05.02.2010
comment
Думаю, вы получите больше ответов, если пометите его как t-sql.   -  person adolf garlic    schedule 09.03.2010
comment
буду иметь в виду в следующий раз, но вопрос уже решен ;-)   -  person Dennis G    schedule 12.03.2010

Ответы (2)


arrow_upward
1
arrow_downward 

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
person Quassnoi    schedule 04.02.2010
comment
Интересно, но все, что я получаю с этим, - это сумма текущего идентификатора - без сгруппированного вывода. Мой вывод (заменяю ID текстом): Television 100, Radio 70, Violet 5 и так далее, так что в основном просто цена товара. SSMS 2008 преобразовал запрос в WITH q AS (SELECT Text, parentID, Price FROM dbo.testOld UNION ALL SELECT p.Text, p.parentID, NULL AS nullablecolumn FROM q AS q_2 INNER JOIN dbo.testOld AS p ON p.ID = q_2.parentID) SELECT Text, SUM (Price) AS sumprice FROM q AS q_1 GROUP BY Text - person Dennis G; 05.02.2010
comment
@moontear: верно, в запросе была небольшая ошибка. См. Сообщение об обновлении. - person Quassnoi; 05.02.2010
comment
Немного серьезного мастерства в SQL! К сожалению, это не то решение, которое я ищу. Вы предлагаете правильный способ создания сумм на уровне иерархии, но что мне действительно нужно, так это способ создания группировок на уровне иерархии. Мне нужно иметь возможность группироваться по уровням, чтобы я мог позволить службам отчетности позаботиться о подсчете сумм. Это возможно с моим вторым примером структуры данных - у меня просто нет этой структуры, и мне понадобится способ временно создать эту структуру или каким-то образом проинструктировать службы отчетов использовать разные группы. - person Dennis G; 05.02.2010

arrow_upward
0
arrow_downward

Я нашел действительно уродливый способ делать то, что хочу - может есть что-то получше?

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

С этой структурой я могу пойти дальше и создать 4 вложенные группы в столбцах LEV0-3, включая промежуточные итоги для каждой группы (как показано выше в моем желаемом результате).

person Dennis G    schedule 08.02.2010