更新Oracle SQL - 使用重复值的表格

Question

我希望有人能提供帮助。我需要使用重复项更新选择表。

ID;CLASS;VALUE;NEW 
1;a;a3; 
1;b;s6; 
1;c;b99; 
2;a;s3; 
2;b;r6; 
2;c;b99; 
3;a;s5; 
4;a;r6; 
4;b;a3; 

看看我的示例表，有一个科拉姆新我必须更新。在该示例中，手动填充列NEW。

这里是目标（见表COL​​ NEW）：

经由ID 1.find重复（HAVING COUNT（*）> 1或类似的东西）

1. UPDATE TABLE SET NEW = CLASS || '_'||价值 WHERE CLASS ='a'或'b'

对您而言简单吗？

Thx

开始=”>

Answer 1

背后的逻辑并不完全清楚;这可能是一种方法。

设置：

create table yourTable(id, class, value, new) as 
(
    select 1, 'a', 'a3', cast (null as varchar2(10)) from dual union all 
    select 1, 'b', 's6', null from dual union all 
    select 1, 'c', 'b99', null from dual union all 
    select 2, 'a', 's3', null from dual union all 
    select 2, 'b', 'r6', null from dual union all 
    select 2, 'c', 'b99', null from dual union all 
    select 3, 'a', 's5', null from dual union all 
    select 4, 'a', 'r6', null from dual union all 
    select 4, 'b', 'a3', null from dual 
) 

查询：

merge into yourTable t1 
using (
     select listagg(value, '_') within group (order by class) as new, 
       id  
     from yourTable 
     where class in ('a', 'b') 
     group by id 
     having count(distinct class) = 2 
    ) t2 
on ( t1.id = t2.id 
    and t1.class in ('a', 'b') 
    ) 
when matched then 
update set t1.new = t2.new 

结果：

SQL> select * 
    2 from yourTable; 

     ID C VAL NEW 
---------- - --- ---------- 
     1 a a3 a3_s6 
     1 b s6 a3_s6 
     1 c b99 
     2 a s3 s3_r6 
     2 b r6 s3_r6 
     2 c b99 
     3 a s5 
     4 a r6 r6_a3 
     4 b a3 r6_a3 

9 rows selected.