排列组合去重的一个想法

[sql] 
SQL> drop table test;  
   
Table dropped  
SQL> create table test(id int,t1 varchar(10),t2 varchar(10),t3 varchar(10));  
   
Table created  
SQL> insert into test(id,t1,t2,t3)  
  2  values(1,'1','3','2');  
   
1 row inserted  
SQL> insert into test(id,t1,t2,t3)  
  2  values(2,'1','3','2');  
   
1 row inserted  
SQL> insert into test(id,t1,t2,t3)  
  2  values(3,'3','2','1');  
   
1 row inserted  
SQL> insert into test(id,t1,t2,t3)  
  2  values(4,'4','2','1');  
   
1 row inserted  
SQL>





 select id,b,row_number() over (partition by b order by id) as sn  
  2  from  
  3  (  
  4  select id,wmsys.wm_concat(b2) as b  
  5  from  
  6  (  
  7  select id,b2 from test  
  8  unpivot(b2 for b3 in (t1,t2,t3))  
  9  order by 1,2  
 10  )  
 11  group by id  
 12  );  
   
     ID        B                SN  
-------------- -------------------  
      1        1,3,2             1  
      2        1,3,2             2  
      3        1,3,2             3  
      4        1,4,2             1  
                
SQL>