Oracle: Listagg

Question

我对SQL很陌生，我试图对分散在几行中的一组备注进行分组。具体来说，我想聚合RMK_TEXT列并按RMK_LINENO排序。

有人能帮我吗？

SELECT
ALL_AGREEMENTS.AGMT_NUM,
PROVISION_TYPES.STIP_TYPE_DESC,
GENERAL_REMARK_TEXT.RMK_LINENO,
LISTAGG(GENERAL_REMARK_TEXT.RMK_TEXT) WITHIN GROUP (ORDER BY GENERAL_REMARK_TEXT.RMK_LINENO) RMK_TEXT
FROM
  STIPULATION_PROVISIONS,
  ALL_AGREEMENTS,
  STIPULATION_TYPES  PROVISION_TYPES,
  GENERAL_REMARK_TEXT,
  GENERAL_REMARKS
WHERE
  ( ALL_AGREEMENTS.ARRG_KEY=STIPULATION_PROVISIONS.ARRG_KEY(+)  )
  AND  ( STIPULATION_PROVISIONS.STIP_TYPE_CODE=PROVISION_TYPES.STIP_TYPE_CODE(+)  )
  AND  ( PROVISION_TYPES.STIP_CATG_CODE = 'PRV'  )
  AND  ( GENERAL_REMARKS.GEN_RMK_FK(+)=STIPULATION_PROVISIONS.STIP_KEY AND GENERAL_REMARKS.RMK_TYPE_CATG(+) = 'PRV'  )
  AND  ( GENERAL_REMARKS.RMK_KEY = GENERAL_REMARK_TEXT.RMK_KEY(+)  )
  AND  ( GENERAL_REMARKS.RMK_TYPE_CATG (+)  = 'PRV'  )
  AND  ( ( ALL_AGREEMENTS.ARRG_ORG_KEY IN
  (SELECT ARRG_ORG_KEY 
  FROM BO_USER_DATA_PRIVS
  WHERE OBJECT_TYPE_CD = 'area'
  AND SEC_USER_ID = UPPER('user'))
AND ALL_AGREEMENTS.SUBJ_CODE IN
  (SELECT SUBJ_CODE
  FROM BO_USER_DATA_PRIVS
  WHERE OBJECT_TYPE_CD = 'SUBJ'
  AND SEC_USER_ID = UPPER('user')) )  )
  AND  
  (
   (
    ALL_AGREEMENTS.AGMT_NUM  IN  ( '00003000','00004000','00005001','00005002','00005003','00006000','00007000','00008000','00009000','00010000','00021000','00022000','00023000','00024000','00025000','00026000' )
   )
   AND
   PROVISION_TYPES.STIP_TYPE_DESC  IN  ( 'provision'  )
   AND
   STIPULATION_PROVISIONS.EXST_FLAG  IN  ( 'Y'  )
  )
GROUP BY ALL_AGREEMENTS.AGMT_NUM

Answer 1

就像这样：

SELECT
  a.AGMT_NUM,
  p.STIP_TYPE_DESC,
  s.EXST_FLAG,
  ( SELECT LISTAGG( t.RMK_TEXT, CHR(13) || CHR(10) )
             WITHIN GROUP ( ORDER BY t.RMK_LINENO )
    FROM   GENERAL_REMARKS r
           INNER JOIN GENERAL_REMARK_TEXT t
           ON ( r.RMK_KEY = t.RMK_KEY)
    WHERE  r.GEN_RMK_FK = s.STIP_KEY
    AND    r.RMK_TYPE_CATG = 'PRV'
  ) AS Remarks
FROM
  ALL_AGREEMENTS a
  LEFT OUTER JOIN STIPULATION_PROVISIONS s
  ON ( a.ARRG_KEY = s.ARRG_KEY )
  LEFT OUTER JOIN STIPULATION_TYPES  p
  ON ( s.STIP_TYPE_CODE = p.STIP_TYPE_CODE )
WHERE
    p.STIP_CATG_CODE = 'PRV'
AND  EXISTS ( SELECT 1
              FROM   BO_USER_DATA_PRIVS b
              WHERE  OBJECT_TYPE_CD = 'LAND'
              AND    SEC_USER_ID = UPPER('user')
              AND    a.ARRG_ORG_KEY = b.ARRG_ORG_KEY
            )
AND  EXISTS ( SELECT 1
              FROM   BO_USER_DATA_PRIVS b
              WHERE  OBJECT_TYPE_CD = 'SUBJ'
              AND    SEC_USER_ID = UPPER('user')
              AND    a.SUBJ_CODE = b.SUBJ_CODE
            )
AND  a.AGMT_NUM  IN ( 'OH00003000','OH00004000','OH00005001','OH00005002','OH00005003','OH00006000','OH00007000','OH00008000','OH00009000','OH00010000','OH00021000','OH00022000','OH00023000','OH00024000','OH00025000','OH00026000' )
AND  p.STIP_TYPE_DESC  = 'provision'
AND  s.EXST_FLAG  = 'Y';