Oracle Database 11g企业版11.2.0.4.0中的STRAGG-64位生产 [英] STRAGG in Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

问题描述

我想在此环境中使用STRAGG函数:Oracle数据库11g企业版11.2.0.4.0版-64位生产，PL/SQL版本11.2.0.4.0-生产，CORE 11.2.0.4.0生产，TNS对于Linux:版本11.2.0.4.0-生产，NLSRTL版本11.2.0.4.0-生产:

I want to use STRAGG function in this env.: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, PL/SQL Release 11.2.0.4.0 - Production, CORE 11.2.0.4.0 Production, TNS for Linux: Version 11.2.0.4.0 - Production, NLSRTL Version 11.2.0.4.0 - Production:

我有一个名为 V_USER_ROLE_DESC

LOGIN   DESC      

joanet  BS in ANDROID.C.3
joanet  DOB in ANDROID.C.3
joanet  DO in ANDROID.C.3
joanet  BS in ANDROID.C.4
joanet  UA in ANDROID.C.4
joanet  OV in ANDROID.C.4
joanet  OI in ANDROID.C.4
joanet  DO in ANDROID.C.4
joanet  DHoU in ANDROID.C.4
joanet  AOP in ANDROID.C.4

执行此查询

select
  login ,
  sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
from
  V_USER_ROLE_DESC
where login = 'joanet'
group by
  login
;

这是结果:

joanet  BS in ANDROID.C.3 - DOB in ANDROID.C.3 - DO in ANDROID.C.3 - BS in ANDROID.C.4 - UA in ANDROID.C.4 - OV in ANDROID.C.4 - OI in ANDROID.C.4 - DO in ANDROID.C.4 - DHoU in ANDROID.C.4 - AOP in ANDROID.C.4 - 

但是没有where子句...

But without the where clause ...

   select
      login ,
      sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
    from
      V_USER_ROLE_DESC
    --where login = 'joanet'
    group by
      login
    ;

这是结果:

...
colau   DOB in ANDROID.D.2 - 
joanet  DOB in ANDROID.D.2 - 
sisa    DOB in ANDROID.D.2 - 
...

使用

SELECT login ,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN;

我有这个错误

ORA-01489:字符串连接的结果太长

推荐答案

我建议使用LISTAGG代替STRAGG. LISTAGG是优越的，因为它允许指定汇总项目的顺序.

I would suggest using LISTAGG instead of STRAGG. LISTAGG is superior because it allows to specify order of aggregated items.

SELECT login
      ,listagg(unit_role_desc,' - ')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = 'joanet'
 GROUP BY LOGIN;

另请参见工作示例:

WITH
 v_user_role_desc AS (
 SELECT 'sisa' login
        ,'BS in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT 'sisa' login
        ,NULL UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT 'joanet' login
        ,'BS in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DOB in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DO in ANDROID.C.3' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'BS in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'UA in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'OV in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'OI in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DO in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'DHoU in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT 'joanet' login
        ,'AOP in ANDROID.C.4' UNIT_ROLE_DESC 
    FROM dual) 
SELECT login
      ,listagg(unit_role_desc,' - ')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = 'joanet'
 GROUP BY LOGIN;

这篇关于Oracle Database 11g企业版11.2.0.4.0中的STRAGG-64位生产的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！