首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >使用listagg选择不同的值

问使用listagg选择不同的值
EN

Stack Overflow用户
提问于 2020-06-03 19:23:17
回答 3查看 49关注 0票数 0

我正在使用oracle 12c数据库。我正在尝试用子查询方法通过listagg选择不同的值。但是我得到了一个错误"ORA-01427:单行子查询返回多行01427. 00000 -“单行子查询返回多行”“。

下面是我的代码:

代码语言:javascript
复制
SELECT DISTINCT nr.sd_id,
            ( select 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 (select distinct r_num from sg_nr) )
FROM   sg_nr nr;
EN

回答 3

Stack Overflow用户

发布于 2020-06-03 19:27:03

请使用以下查询,

代码语言:javascript
复制
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;
票数 0
EN

Stack Overflow用户

发布于 2020-06-03 19:33:52

您不需要windows子句(OVER)。只需在内部子查询中使用WHERE子句,如下所示:

代码语言:javascript
复制
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;

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

代码语言:javascript
复制
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;
票数 0
EN

Stack Overflow用户

发布于 2020-06-03 20:17:01

性能透视图可能如下所示,因为您已经在使用相同的表SG_NR。然后使用带有MATERIALIZE提示的LISTAGG函数。

代码语言:javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/62171629

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档