多個連結表的sql問題

我有一個 Oracle 數據庫和下表：

raportor:
id      id_pct_lucru
58382   327342

ses_rap
id  an      status
1   2012    I
21  2013    I
41  2014    D

ch_trat
id      fk_id_raportor  FK_ID_SR
20128   58382           41

taed
cod_deseu   cant    fk_trat_id
12.01.06    137     20128
12.01.13    50      20128
12.01.06    22      20128

tid
id      den     cod_tip
3557    dep3    HS
3555    dep1    B
3556    dep2    HZC

ticd
fk_inst_trat_id     cod
3557                12.01.06
3556                12.01.13
3555                12.01.06

和以下sql：

SELECT RAP.ID_PCT_LUCRU as id_pl, 
         ses_rap.an_raportare as an,
         taed.COD_DESEU as cod,
         sum(taed.cant) as cant,
         LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP) AS tip_op,
    FROM ch_trat cht
         INNER JOIN raportor rap
            ON cht.fk_id_raportor = rap.id
         INNER JOIN ses_rap
            ON ses_rap.id = CHT.FK_ID_SR
         INNER JOIN taed
            ON taed.FK_TRAT_ID = CHT.ID
         INNER JOIN tid
            ON tid.FK_TRAT_ID = CHT.ID
         INNER JOIN ticd
            ON TICD.FK_INST_TRAT_ID = TID.ID
            and TICD.COD=taed.COD_DESEU
   WHERE SES_RAP.STATUS = 'D'
       group by RAP.ID_PCT_LUCRU,
           SES_RAP.AN_RAPORTARE,
           CHT.ID,
           taed.COD_DESEU
    order by RAP.ID_PCT_LUCRU

結果是：

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    318     B,B,HS,HS
327342  2014    12.01.13    50      HZC

但我想要結果：

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    159     B,HS
327342  2014    12.01.13    50      HZC

我不知道該怎麼做。請幫忙！

我是這樣做的：

SELECT RAP.ID_PCT_LUCRU as id_pl,
         ses_rap.an_raportare as an,
         taed.COD_DESEU as cod,
         (select sum(TAED2.CPREL) from taed taed2
           where TAED2.COD_DESEU=TAED.COD_DESEU and TAED2.FK_TRAT_ID=CHT.ID
         ) as cant, 
         unique_list(LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP)) AS tip_op
    FROM ch_trat cht
         INNER JOIN raportor rap
            ON cht.fk_id_raportor = rap.id
         INNER JOIN ses_rap
            ON ses_rap.id = CHT.FK_ID_SR
         INNER JOIN taed
            ON taed.FK_TRAT_ID = CHT.ID
         inner JOIN tid
            ON tid.FK_TRAT_ID = CHT.ID
         inner JOIN ticd
            ON TICD.FK_INST_TRAT_ID = TID.ID
            and TICD.COD=taed.COD_DESEU
   WHERE SES_RAP.STATUS = 'D' AND taed.COD_DESEU IS NOT NULL
       group by RAP.ID_PCT_LUCRU,
           SES_RAP.AN_RAPORTARE,
           CHT.ID,
           TAED.COD_DESEU
    order by RAP.ID_PCT_LUCRU

對於您的 SUM 問題，請參閱本文以獲取可能有用的 Oracle 解決方案的詳細說明 –> https://community.oracle.com/thread/2137764?tstart=0

對於重複值和來自其他列的值的 LISTAGG 問題，請查看本文以獲取一些可能滿足您需要的解決方案 –> https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return -不同的值

通過一些快速閱讀而不是 Oracle SQL 人員，這似乎有幾件事可能會有所幫助，但我無法輕鬆地提供一個範例查詢供您目前使用，不幸的是，您提供的這個表數據範例.

希望這對我看到的兩個問題有所幫助或為您指明正確的方向，即您獲得的結果和需要的結果。

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