with
masterTab as (
select 1 as idMaster, 123 as value from dual union all
select 2 as idMaster, 132 as value from dual union all
select 3 as idMaster, 342 as value from dual union all
select 4 as idMaster, 343 as value from dual union all
select 5 as idMaster, 124 as value from dual union all
select 6 as idMaster, 545 as value from dual
),
detailTab as (
select 1 as idMaster, 2 as rec from dual union all
select 1 as idMaster, 3 as rec from dual union all
select 1 as idMaster, 4 as rec from dual union all
select 2 as idMaster, 2 as rec from dual union all
select 2 as idMaster, 3 as rec from dual union all
select 2 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 2 as rec from dual union all
select 3 as idMaster, 3 as rec from dual union all
select 3 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 5 as rec from dual
)
select
decode(grouping_id(b.rec), 1, 1, b.rec) rec,
decode(grouping_id(b.rec),
1, sum(decode(nvl(rn, 1), 1, value)),
sum(value)
) sm
from masterTab a
left join (select t.*, row_number() over (partition by idMaster order by null) rn
from detailTab t
) b on a.idMaster = b.idMaster
group by rollup(b.rec)
having grouping_id(b.rec) = 1 or b.rec is not null
order by 1;
REC SM
---------- ----------
1 1609
2 597
3 597
4 597
5 342
План выполнения
----------------------------------------------------------
Plan hash value: 1095109097
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 150 | 36 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 150 | 36 (12)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | SORT GROUP BY ROLLUP| | 6 | 150 | 36 (12)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 6 | 150 | 34 (6)| 00:00:01 |
| 5 | VIEW | | 6 | 36 | 12 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | VIEW | | 10 | 190 | 21 (5)| 00:00:01 |
| 14 | WINDOW SORT | | 10 | 60 | 21 (5)| 00:00:01 |
| 15 | VIEW | | 10 | 60 | 20 (0)| 00:00:01 |
| 16 | UNION-ALL | | | | | |
| 17 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 18 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 19 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 20 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 21 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 22 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 23 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 24 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 25 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 26 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("B"."REC",1,0,SYS_O
P_BITVEC)))=1 OR "B"."REC" IS NOT NULL)
4 - access("A"."IDMASTER"="B"."IDMASTER"(+))
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
451 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed |