Oracle 在SQL中将列号值转换为数组号值

我有一张下面的表。

create table t2 ( id varchar2(1),val number) ;

insert into t2 values ('a',1);
insert into t2 values ('a',2);
insert into t2 values ('a',3);
insert into t2 values ('a',4);

insert into t2 values ('b',1);
insert into t2 values ('b',2);
insert into t2 values ('b',3);

insert into t2 values ('c',1);
insert into t2 values ('c',2);
insert into t2 values ('c',4);

insert into t2 values ('d',1);
insert into t2 values ('d',2);

我们必须像下面一样打印o/p。

id   x 
---  -------
a    1,2,3,4
b    1,2,3
c    1,2,4
d    1,2

这可以通过以下查询来实现

select id,LISTAGG(val, ',') WITHIN GROUP (ORDER BY val ) as x 
from t2
group by id

这里x列是字符数据类型。但是我需要将其转换为数字的varray/数字的嵌套表 (不是字符的varray/nestedtable)。

我试过下面

CREATE TYPE varchar_TT AS TABLE OF varchar(10);

with z as (            
 select id,varchar_TT(LISTAGG(val, ',') WITHIN GROUP (ORDER BY val)) as x ,varchar_TT('1,2') y
from t2
 group by id )
 select id , x ,y from z ;

o/p 
----
id            x                                 y  
----     -------------                   ---------------
a C##SIVA.VARCHAR_TT('1,2,3,4')C##SIVA.VARCHAR_TT('1,2')
b C##SIVA.VARCHAR_TT('1,2,3') C##SIVA.VARCHAR_TT('1,2')
c C##SIVA.VARCHAR_TT('1,2,4') C##SIVA.VARCHAR_TT('1,2')
d C##SIVA.VARCHAR_TT('1,2') C##SIVA.VARCHAR_TT('1,2')

如果我添加以下条件，则不会得到任何结果。

where y member of x ;

所以我试图转换为数字数组。

CREATE TYPE number_TT AS TABLE OF number;

with z as (            
 select id,number_TT(LISTAGG(val, ',') WITHIN GROUP (ORDER BY val)) as x ,number_TT(1,2)y
from t2
 group by id )
 select id , x ,y from z ;

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

1) 在这里我需要像下面这样的o/p来使用member和submultiset条件。

o/p 
----
id            x                                 y  
----     -------------                   ---------------
a C##SIVA.NUMBER_TT(1,2,3,4) C##SIVA.NUMBER_TT(1,2)
b C##SIVA.NUMBER_TT(1,2,3) C##SIVA.NUMBER_TT(1,2)
c C##SIVA.NUMBER_TT(1,2,4) C##SIVA.NUMBER_TT(1,2)
d C##SIVA.NUMBER_TT(1,2)C##SIVA.NUMBER_TT(1,2)

 select varchar_tt('1,2') x ,number_TT(1,2)y  from dual;

       x                             y  
--------------------            ----------------
C##SIVA.VARCHAR_TT('1,2') C##SIVA.NUMBER_TT(1,2)

请让我知道如何将字符数组转换为数字数组。

2)

create table t4 ( id VARCHAR2(1) , val number_tt )
 NESTED TABLE val STORE AS val_2 ;

如何从t2插入t4表？

t4表的预期o/p查询应如下所示

id            val                          
----     -------------                   
a C##SIVA.NUMBER_TT(1,2,3,4) 
b C##SIVA.NUMBER_TT(1,2,3) 
c C##SIVA.NUMBER_TT(1,2,4) 
d C##SIVA.NUMBER_TT(1,2)

我得到了类似的输出与下面的查询。

select  id ,  CAST(MULTISET(SELECT val FROM t2 where t2.id=x.id) as number_tt) as val1  from t2 x

id       val1
---    --------------
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
b C##SIVA.NUMBER_TT(1, 2, 3)
b C##SIVA.NUMBER_TT(1, 2, 3)
b C##SIVA.NUMBER_TT(1, 2, 3)
c C##SIVA.NUMBER_TT(1, 2, 4)
c C##SIVA.NUMBER_TT(1, 2, 4)
c C##SIVA.NUMBER_TT(1, 2, 4)
d C##SIVA.NUMBER_TT(1, 2)
d C##SIVA.NUMBER_TT(1, 2)

但是它有重复的。

我试过了distinct但它不起作用。

所以我使用了group by，它正在工作。

select  id ,  CAST(MULTISET(SELECT val FROM t2 where t2.id=x.id) as number_tt) as val1  from t2 x
group by id 

除了上面还有什么办法吗？

我认为您在如何找到匹配的行方面感到困惑。以及如何在最后显示这些行。

Listagg便于最终显示。但这不是在构建数组!只是一串逗号分隔的值。

如果将listagg的输出粘贴到嵌套表中，则数组中有一个元素。这是一个CSV字符串。要将每个值作为数组元素，您需要收集 () 它们在那就做你的比较。

你在处理数字。所以你想比较嵌套的数表。有几种方法可以做到这一点

Member

使用它来查看嵌套表的类型的一个值是否存在

with z as (            
  select id,
         cast ( collect ( val ) as number_TT ) n_array,
         3 n,
         number_TT ( 1, 3 ) ntt,
         listagg ( val, ',' ) within group ( order by val ) vals
  from   t2
  group  by id 
)
 select z.*
 from   z
 where  n member of n_array;

ID    N_ARRAY         N    NTT       VALS      
a     [1, 4, 3, 2]       3 [1, 3]    1,2,3,4    
b     [1, 3, 2]          3 [1, 3]    1,2,3  

Submutliset

使用它来检查一个嵌套表中的所有元素是否存在于另一个嵌套表中。

with z as (            
  select id,
         cast ( collect ( val ) as number_TT ) n_array,
         3 n,
         number_TT ( 1, 3 ) ntt,
         listagg ( val, ',' ) within group ( order by val ) vals
  from   t2
  group  by id 
)
 select z.*
 from   z
 where  ntt submultiset of n_array;

ID    N_ARRAY         N    NTT       VALS      
a     [1, 4, 3, 2]       3 [1, 3]    1,2,3,4    
b     [1, 3, 2]          3 [1, 3]    1,2,3  

注意-在结果中显示类似于数组的嵌套表是SQL Developer对此的处理。不同的客户会有不同的方式来展示这些!

当然，我假设您出于某种原因需要在这里使用嵌套表。这里的问题是一种关系划分形式。如果你想的话，你可以用纯SQL解决:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534489800346122396