请使用下面的查询，

SELECT DISTINCT nr.sd_id, listagg(nr.r_num,' ') within group (ORDER BY 
Nlssort(nr.r_num, 'NLS_SORT=BINARY_CI')
OVER ( PARTITION BY nr.sd_id, nr.rid, nr.rr_id) r_num_group FROM  sg_nr nr;

你不需要windows子句( OVER ). 就用这个 WHERE 内部子查询中的子句如下：

SELECT DISTINCT NR.SD_ID,
                ( SELECT LISTAGG(NR.R_NUM,' ') WITHIN GROUP(
                         ORDER BY NLSSORT( NR.R_NUM, 'NLS_SORT=BINARY_CI')) 
                      FROM (
                        SELECT DISTINCT R_NUM
                          FROM SG_NR NRIN
                         WHERE NRIN.SD_ID = NR.SD_ID
                           AND NRIN.RID = NR.RID
                           AND NRIN.RR_ID = NR.RR_ID)) AS R_NUM_GROUP
  FROM SG_NR NR;

最好的方法是使用子查询作为内部视图，并将其与主表连接，如下所示：

SELECT NR.SD_ID, NRIN.R_NUM_GROUP
  FROM SG_NR NR
  JOIN (
    SELECT NRIN.SD_ID, NRIN.RID, NRIN.RR_ID,
           LISTAGG(NRIN.R_NUM, ' ') WITHIN GROUP(
                ORDER BY NLSSORT( NRIN.R_NUM, 'NLS_SORT=BINARY_CI' )
           ) R_NUM_GROUP
      FROM ( SELECT DISTINCT NRIN.R_NUM, NRIN.SD_ID, NRIN.RID, NRIN.RR_ID
               FROM SG_NR NRIN) NRIN
      GROUP BY NRIN.SD_ID, NRIN.RID, NRIN.RR_ID
    ) NRIN ON NRIN.SD_ID = NR.SD_ID
          AND NRIN.RID = NR.RID
          AND NRIN.RR_ID = NR.RR_ID;

性能方面的透视图可以是这样的，因为您已经在使用同一个表了 SG_NR . 然后使用 LISTAGG 带有materialize提示的函数。

WITH NR AS (SELECT /*+MATERIALIZE*/
                  * FROM SG_NR)
SELECT NR.SD_ID, NR2.R_NUM_GROUP
  FROM NR
       INNER JOIN
       (  SELECT NR2.SD_ID,
                 NR2.RID,
                 NR2.RR_ID,
                 LISTAGG (
                    NR2.R_NUM,
                    ' ')
                 WITHIN GROUP (ORDER BY
                                  NLSSORT (NR2.R_NUM, 'NLS_SORT=BINARY_CI'))
                    R_NUM_GROUP
            FROM (  SELECT NR2.R_NUM,
                           NR2.SD_ID,
                           NR2.RID,
                           NR2.RR_ID
                      FROM NR NR2
                  GROUP BY NR2.R_NUM,
                           NR2.SD_ID,
                           NR2.RID,
                           NR2.RR_ID) NR2
        GROUP BY NR2.SD_ID, NR2.RID, NR2.RR_ID) NR2
          ON     NR2.SD_ID = NR.SD_ID
             AND NR2.RID = NR.RID
             AND NR2.RR_ID = NR.RR_ID