Oracle 按汇总分组

下午好，汤姆!我在中间总数的查询中遇到了困难 (组将汇总)。您可以告诉如何将具有条件应用于中间结果。
总和 (Proc)<7

with query1 as 
(
     select '123' schet, '1111101' INN, 'OrgName' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Ranch1' fil from dual
     union
     select '124' , '1111102' , 'OrgName1' , 1.01 , 1.02 , 6.23 , 'Ranch2'  from dual
     union
     select '125' , '1111103' , 'OrgName3' , 2.02 , 2.02 , 2.02 , 'Ranch1'  from dual
     union
     select '126' , '1111103' , 'OrgName33' , 3.03 , 3.03 , 3.03 , 'Ranch3'  from dual
     union
     select '127' , '1111105' , 'OrgName11' , 4.04 , 4.04 , 4.24 , 'Ranch1'  from dual
     union
     select '128' , '1111105' , 'OrgName12'  , 5.05 , 5.05 , 5.25 , 'Ranch2'  from dual
     union
     select '129' , '1111107' , 'OrgName111' , 6.06 , 6.06 , 6.26 , 'Ranch1'  from dual
     union
     select '130' , '1111107' , 'OrgName122' , 7.07 , 7.07 , 7.27 , 'Ranch2'  from dual
     union
     select '131' , '1111107' , 'OrgName133' , 8.08 , 8.08 , 8.28 , 'Ranch3'  from dual

)
select * 
from (
    select INN, 
           decode(grouping_id(INN,nameorg),1,'Всего по INN '||INN||':',3,'Итого:',nameorg) nameorg, 
           schet, 
           fil,
           sum(SummAll) SummAll, 
           sum(Summ1) Summ1, 
           sum(Proc) Proc,
           decode(grouping_id(INN,nameorg),3,1,0) seq_1,
           decode(grouping_id(INN,nameorg),1,1,0) seq_2
    from query1
    group by rollup(INN, (nameorg, schet, fil))
-- having Proc < 7
    order by seq_1,INN,seq_2,nameorg
)
复制

我需要结果

1 1111101 OrgName   123   Ranch1    12.12    123.12   12.04   0 0
2 1111101 Total for INN 1111101:    12.12    123.12   12.04 0 1
3 1111102 OrgName1  124   Ranch2    1.01     1.02     6.23 0 0
4 1111102 Total for INN 1111102:    1.01     1.02     6.23 0 1
5 1111103 OrgName3  125   Ranch1    2.02     2.02     2.02 0 0
6 1111103 OrgName33 126   Ranch3    3.03     3.03     3.03 0 0
7 1111103 Total for TIN 1111103:    5,05     5,05     5,05 0 1
复制

所以你想显示小计小于某个值的所有行，加上小于该值的小计行？

如果是这样，您可以通过分析来做到这一点!

在结果上，如果您的汇总，则获取每个 “INN” 和当前分组级别的总和。您可以通过在分组列中添加添加grouping_id来做到这一点:

grouping_id (  inn, nameorg, schet, fil )
复制

然后按inn和分组级别使用:

sum(proc) over (partition by inn, grp)
复制

然后，您可以过滤该总和的结果以找到该总和 <7的结果:

with query1 as
  (select '123' schet, '1111101' inn, 'OrgName' nameorg,
    12.12 summall, 123.12 summ1, 4.04 proc,
    'Ranch1' fil
  from dual  union
  select '124' , '1111102' , 'OrgName1' ,
    1.01 , 1.02 , 6.23 ,
    'Ranch2'
  from dual  union
  select '125' , '1111103' , 'OrgName3' ,
    2.02 , 2.02 , 2.02 ,
    'Ranch1'
  from dual  union
  select '126' , '1111103' , 'OrgName33' ,
    3.03 , 3.03 , 3.03 ,
    'Ranch3'
  from dual  union
  select '127' , '1111105' , 'OrgName11' ,
    4.04 , 4.04 , 4.24 ,
    'Ranch1'
  from dual  union
  select '128' , '1111105' , 'OrgName12' ,
    5.05 , 5.05 , 5.25 ,
    'Ranch2'
  from dual  union
  select '129' , '1111107' , 'OrgName111' ,
    6.06 , 6.06 , 6.26 ,
    'Ranch1'
  from dual  union
  select '130' , '1111107' , 'OrgName122' ,
    7.07 , 7.07 , 7.27 ,
    'Ranch2'
  from dual  union
  select '131' , '1111107' , 'OrgName133' ,
    8.08 , 8.08 , 8.28 ,
    'Ranch3'
  from dual
  ), grps as (
select *
from
  (select inn, 
    decode ( grouping_id ( inn,nameorg ) ,1,'Всего по INN '||inn ||':',3,'Итого:',nameorg ) nameorg, 
    schet, fil,
    sum ( summall ) summall, 
    sum ( summ1 ) summ1, 
    sum ( proc ) proc,
    decode(grouping_id(INN,nameorg),3,1,0) seq_1,
    decode(grouping_id(INN,nameorg),1,1,0) seq_2,
    grouping_id (  inn, nameorg, schet, fil ) grp
  from query1
  group by rollup ( inn, ( nameorg, schet, fil ) )
  )
), tots as (
  select grps.*, sum(proc) over (partition by inn, grp) sm from grps
)
  select * from tots
  where  sm < 7
  order by inn nulls last,grp,nameorg;

INN      NAMEORG                SCHET  FIL     SUMMALL  SUMM1   PROC  SEQ_1  SEQ_2  GRP  SM    
1111101  OrgName                123    Ranch1  12.12    123.12  4.04  0      0      0    4.04  
1111101  ????? ?? INN 1111101:                 12.12    123.12  4.04  0      1      7    4.04  
1111102  OrgName1               124    Ranch2  1.01     1.02    6.23  0      0      0    6.23  
1111102  ????? ?? INN 1111102:                 1.01     1.02    6.23  0      1      7    6.23  
1111103  OrgName3               125    Ranch1  2.02     2.02    2.02  0      0      0    5.05  
1111103  OrgName33              126    Ranch3  3.03     3.03    3.03  0      0      0    5.05  
1111103  ????? ?? INN 1111103:                 5.05     5.05    5.05  0      1      7    5.05 
复制