ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / (11.2.0.4)Очередное добавление итогов
4 сообщений из 4, страница 1 из 1
(11.2.0.4)Очередное добавление итогов
    #39653549
Фотография Есть вопрос
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть вопрос
Участник
Здравствуйте!
Есть структура подобного вида:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
WITH a AS
 (SELECT 1 AS rn, 'A' AS cde, 10 AS val
    FROM dual u),
b AS
 (SELECT 2 AS rn, 1 AS prn, 'B' AS cde, 3 AS val
    FROM dual
  UNION ALL
  SELECT 3 AS rn, 1 AS prn, 'C' AS cde, 6 AS val
    FROM dual
  UNION ALL
  SELECT 8 AS rn, 1 AS prn, 'BB' AS cde, 1 AS val
    FROM dual),
c AS
 (SELECT 4 AS rn, 2 AS prn, 'B' AS cde, 1 AS val
    FROM dual
  UNION ALL
  SELECT 5 AS rn, 2 AS prn, 'E' AS cde, 3 AS val
    FROM dual
  UNION ALL
  SELECT 6 AS rn, 3 AS prn, 'F' AS cde, 2 AS val
    FROM dual
  UNION ALL
  SELECT 7 AS rn, 3 AS prn, 'G' AS cde, 5 AS val
    FROM dual
  UNION ALL
  SELECT 9 AS rn, 8 AS prn, 'BB' AS cde, 1 AS val
    FROM dual)
SELECT *
  FROM a
  LEFT OUTER JOIN b
    ON a.rn = b.prn
  LEFT OUTER JOIN c
    ON b.rn = c.prn


Код: plaintext
1.
2.
3.
4.
5.
6.
RN	CDE	VAL	RN	PRN	CDE	VAL	RN	PRN	CDE	VAL
1	A	10	2	1	B	3	4	2	B	1
1	A	10	2	1	B	3	5	2	E	3
1	A	10	3	1	C	6	6	3	F	2
1	A	10	3	1	C	6	7	3	G	5
1	A	10	8	1	BB	1	9	8	BB	1

Т.е, несколько таблиц, в данном случае A,B,C где записи связаны от A к C по PRN=RN. Это, по сути, превращается в дерево:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
WITH a AS
 (SELECT 1 AS rn, 'A' AS cde, 10 AS val
    FROM dual u),
b AS
 (SELECT 2 AS rn, 1 AS prn, 'B' AS cde, 3 AS val
    FROM dual
  UNION ALL
  SELECT 3 AS rn, 1 AS prn, 'C' AS cde, 6 AS val
    FROM dual
  UNION ALL
  SELECT 8 AS rn, 1 AS prn, 'BB' AS cde, 1 AS val
    FROM dual),
c AS
 (SELECT 4 AS rn, 2 AS prn, 'B' AS cde, 1 AS val
    FROM dual
  UNION ALL
  SELECT 5 AS rn, 2 AS prn, 'E' AS cde, 3 AS val
    FROM dual
  UNION ALL
  SELECT 6 AS rn, 3 AS prn, 'F' AS cde, 2 AS val
    FROM dual
  UNION ALL
  SELECT 7 AS rn, 3 AS prn, 'G' AS cde, 5 AS val
    FROM dual
  UNION ALL
  SELECT 9 AS rn, 8 AS prn, 'BB' AS cde, 1 AS val
    FROM dual),
t AS
 (SELECT rn, 0 AS prn, cde, val
    FROM a
  UNION
  SELECT *
    FROM b
  UNION
  SELECT *
    FROM c)
/*-------------------------*/
SELECT t.*, LEVEL AS lvl, sys_connect_by_path(t.cde, '/') AS pth
  FROM t
CONNECT BY PRIOR t.rn = t.prn
 START WITH t.prn = 0


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
RN	PRN	CDE	VAL	LVL	PTH
1	0	A	10	1	/A
2	1	B	3	2	/A/B
4	2	B	1	3	/A/B/B
5	2	E	3	3	/A/B/E
3	1	C	6	2	/A/C
6	3	F	2	3	/A/C/F
7	3	G	5	3	/A/C/G
8	1	BB	1	2	/A/BB
9	8	BB	1	3	/A/BB/BB


Задача: для формирования отчета, в цикле по таблице A, вывести итоги по VAL для дочерних записей от A к B и C с подытогами, т.е вот это:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT x.lvl
      ,x.prn
      ,x.cde
      ,decode(GROUPING(x.cde) + GROUPING(x.prn), 1,
              (SELECT t.cde
                  FROM t
                 WHERE t.rn = x.prn), NULL) AS grp
      ,GROUPING(x.cde)
      ,GROUPING(x.prn)
      ,SUM(x.val) AS val
  FROM (SELECT t.*, LEVEL AS lvl, sys_connect_by_path(t.cde, '/') AS pth
          FROM t
        CONNECT BY PRIOR t.rn = t.prn
         START WITH t.prn = 0) x
 GROUP BY ROLLUP(x.lvl, x.prn, x.cde)


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
1	0	A		0	0	10
1	0			1	0	10
1				1	1	10
2	1	B		0	0	3
2	1	C		0	0	6
2	1	BB		0	0	1
2	1		A	1	0	10
2				1	1	10
3	2	B		0	0	1
3	2	E		0	0	3
3	2		B	1	0	4
3	3	F		0	0	2
3	3	G		0	0	5
3	3		C	1	0	7
3	8	BB		0	0	1
3	8		BB	1	0	1
3				1	1	12
				1	1	32

...превратить в это:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
A	10		10		12
		B	3		4
				B	1
				E	3
		C	6		7
				F	2
				G	5
		BB	1	BB	1
...
Рейтинг: 0 / 0
31.05.2018, 19:04
| Ответить | Цитировать | Написать  
(11.2.0.4)Очередное добавление итогов
    #39653712
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Есть вопрос,

надо допилить к нужному результату
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
  1  WITH a AS
  2   (SELECT 1 AS rn, 'A' AS cde, 10 AS val
  3      FROM dual u),
  4  b AS
  5   (SELECT 2 AS rn, 1 AS prn, 'B' AS cde, 3 AS val
  6      FROM dual
  7    UNION ALL
  8    SELECT 3 AS rn, 1 AS prn, 'C' AS cde, 6 AS val
  9      FROM dual
 10    UNION ALL
 11    SELECT 8 AS rn, 1 AS prn, 'BB' AS cde, 1 AS val
 12      FROM dual),
 13  c AS
 14   (SELECT 4 AS rn, 2 AS prn, 'B' AS cde, 1 AS val
 15      FROM dual
 16    UNION ALL
 17    SELECT 5 AS rn, 2 AS prn, 'E' AS cde, 3 AS val
 18      FROM dual
 19    UNION ALL
 20    SELECT 6 AS rn, 3 AS prn, 'F' AS cde, 2 AS val
 21      FROM dual
 22    UNION ALL
 23    SELECT 7 AS rn, 3 AS prn, 'G' AS cde, 5 AS val
 24      FROM dual
 25    UNION ALL
 26    SELECT 9 AS rn, 8 AS prn, 'BB' AS cde, 1 AS val
 27      FROM dual),
 28  t AS
 29   (SELECT rn, 0 AS prn, cde, val
 30      FROM a
 31    UNION
 32    SELECT *
 33      FROM b
 34    UNION
 35    SELECT *
 36      FROM c)
 37  /*-------------------------*/
 38  SELECT t.*, LEVEL AS lvl, sys_connect_by_path(t.cde, '/') AS pth
 39  ,(select sum(val) from t tt
 40    CONNECT BY PRIOR tt.rn = tt.prn
 41    START WITH tt.rn =t.rn ) sum_pth
 42    FROM t
 43  CONNECT BY PRIOR t.rn = t.prn
 44*  START WITH t.prn = 0
SQL>
SQL> /

        RN        PRN CD        VAL        LVL PTH           SUM_PTH
---------- ---------- -- ---------- ---------- ---------- ----------
         1          0 A          10          1 /A                 32
         2          1 B           3          2 /A/B                7
         4          2 B           1          3 /A/B/B              1
         5          2 E           3          3 /A/B/E              3
         3          1 C           6          2 /A/C               13
         6          3 F           2          3 /A/C/F              2
         7          3 G           5          3 /A/C/G              5
         8          1 BB          1          2 /A/BB               2
         9          8 BB          1          3 /A/BB/BB            1

9 rows selected.



.....
stax
...
Рейтинг: 0 / 0
01.06.2018, 08:57
| Ответить | Цитировать | Написать  
(11.2.0.4)Очередное добавление итогов
    #39653826
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Есть вопрос,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
t AS
 (SELECT rn, 0 AS prn, cde, val
    FROM a
  UNION
  SELECT *
    FROM b
  UNION
  SELECT *
    FROM c)



Это продуманный UNION или попытка потерять данные?
...
Рейтинг: 0 / 0
01.06.2018, 11:25
| Ответить | Цитировать | Написать  
(11.2.0.4)Очередное добавление итогов
    #39653850
Фотография Есть вопрос
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть вопрос
Участник
MaximaXXL,

Продуманная небрежность :)
...
Рейтинг: 0 / 0
01.06.2018, 11:44
| Ответить | Цитировать | Написать  
4 сообщений из 4, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / (11.2.0.4)Очередное добавление итогов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=112&tid=1883908]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 35ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 4ms
others: 233ms
total:  359ms
созд. / загр.: 359ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]