分享
三行代码  ›  专栏  ›  技术社区  ›  A A

使用listagg[duplicate]时,Oracle中的“not a single group function” - 'not a single-group group function' in oracle when using LISTAGG [duplicate]

  •  1
  • A A  · 技术社区  · 1 周前

    我的查询是使用ListaGG,它显示了一个错误00937。00000—“不是单组群函数” 我已经经历了许多类似于这个错误的StAcExpRoad问题,这些答案使用一些子查询来解决这个问题。我不想在此查询中编写子查询

    SELECT DISTINCT AMD.UNID AS APPLICATION_REF_ID, 
    LISTAGG(LOC.PLT,',') WITHIN GROUP (ORDER BY LOC.PLT ) AS MYLOC  
    FROM TAB1 AMD
    LEFT JOIN TAB2 PER
    ON NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
    LEFT JOIN TAB3 LOC
    ON NVL(AMD.PERMITNEWID, AMD.PERMITID) = LOC.PERMITID
    WHERE NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
    
    2 回复  |  直到 1 周前
        1
  •  1
  •   ϻᴇᴛᴀʟ    1 周前

    你有一个失踪 group by ,你不需要 distinct() 如果你是按 UNID

    SELECT AMD.UNID AS APPLICATION_REF_ID, 
    LISTAGG(LOC.PLT,',') WITHIN GROUP (ORDER BY LOC.PLT ) AS MYLOC  
    FROM TAB1 AMD
    LEFT JOIN TAB2 PER
    ON NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
    LEFT JOIN TAB3 LOC
    ON NVL(AMD.PERMITNEWID, AMD.PERMITID) = LOC.PERMITID
    WHERE NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
    GROUP BY AMD.UNID
    
        2
  •  1
  •   Rustam Pulatov    1 周前

    尝试分组方式:

    SELECT amd.unid AS application_ref_id,
           LISTAGG (loc.plt, ',') WITHIN GROUP (ORDER BY loc.plt) AS myloc
      FROM tab1 amd
           LEFT JOIN tab2 per ON NVL (amd.permitnewid, amd.permitid) = per.unid
           LEFT JOIN tab3 loc
               ON NVL (amd.permitnewid, amd.permitid) = loc.permitid
     WHERE NVL (amd.permitnewid, amd.permitid) = per.unid
    GROUP BY amd.unid