在有問題的幾個表上使用 group by

我在這樣的 Oracle 數據庫中有一個設計不佳的表：

sr:
id  an_rap
21  2015

rap:
id      cf
28185   123-123

ch:
id      fk_id_rap   FK_ID_SR
12348   28185       21

dma:
ch_id   FK_AMB_ST
12348   18059

cant:
id      FK_ID_MUN   CANT_VAL    COD_VALORIFICARE
18059   12348       18.56       R12     

col:
FK_ID_COL_DMA   CANT_VAL    COD_VALORIFICARE
18059           1134        R10
18059           1234        R3

如果我做了一個：

select
   SR.AN_RAP as AN, RAP.CF,
   max(CANT.CANT_VAL) as CANT_VAL,
   max(CANT.COD_VALORIFICARE) as COD_VALORIFICARE,
   max(COL.CANT_VAL) as kk0,
   max(COL.COD_VALORIFICARE) as kk1
from dma
   inner join ch on ch.id=DMA.CH_ID
   inner join cant on CANT.FK_ID_MUN=ch.id
   full outer join col on cant.id=COL.FK_ID_COL_DMA
   inner join sr on SR.ID=CH.FK_ID_SR
   inner join rap on RAP.ID=CH.FK_ID_RAP
where RAP.CF='123-123'
group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
   CANT.COD_VALORIFICARE,COL.COD_VALORIFICARE
order by sr.an_rap desc,rap.cf,cant.id

結果是：

an      cf          CANT_VAL    COD_VALORIFICARE    kk0     kk1
2015    123-123     18.56       R12                 1134    R10
2015    123-123     18.56       R12                 1234    R3

我怎樣才能做出這樣的事情——不改變數據庫的結構？？？

an      cf          CANT_VAL    COD_VALORIFICARE
2015    123-123     18.56       R12
2015    123-123     1134        R10
2015    123-123     1234        R3

謝謝！

我是這樣解決的：

 select
       SR.AN_RAP as AN, RAP.CF,
       max(col_cant.CANT_VAL) as CANT_VAL,
       max(col_cant.COD_VALORIFICARE) as COD_VALORIFICARE
   from dma
       inner join ch on ch.id=DMA.CH_ID
   inner join
           (select CANT.ID,CANT.FK_ID_MUN,CANT.CANT_VAL,CANT.COD_VALORIFICARE from cant
           union
           select  COL.FK_ID_COL_DMA,CANT2.FK_ID_MUN,COL.CANT_VAL, COL.COD_VALORIFICARE from col 
               inner join cant2 on cant2.id=COL.FK_ID_COL_DMA
           ) col_cant 
      on col_cant.FK_ID_MUN=ch.id
   inner join sr on SR.ID=CH.FK_ID_SR
   inner join rap on RAP.ID=CH.FK_ID_RAP
   where RAP.CF='123-123'
   group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
           col_cant.COD_VALORIFICARE
   order by sr.an_rap desc,rap.cf,col_cant.id

引用自：https://dba.stackexchange.com/questions/120084