一个比较难的分组排序设值的update sql语句问题！

Question

--有表如下，数据如下
create table T1
(
  CUSTOMER_TEL_ID VARCHAR2(40) not null,
  CUSTOMER_ID     VARCHAR2(40),
  TELEPHONE       VARCHAR2(40),
  IS_EXTENTION    NUMBER,
  IS_FEE          NUMBER,
  OWNERSHIP       NUMBER,
  FLAG            VARCHAR2(2)
)
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868012', 'SH02110161344', '58828160', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868013', 'SH02110161344', '58828161', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868009', 'SH02110161344', '58369552', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868011', 'SH02110161344', '58775700', 1, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868014', 'SH02110161344', '58828170', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868010', 'SH02110161344', '58369661', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('868015', 'SH02110161344', '58828171', 0, 0, 1, null);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP, FLAG)
values ('091234', 'SH02110024412', '63600978', 0, 0, 1, null);
commit;
--现在需要给其中字段flag设值。有如下sql取得flag值
 select customer_tel_id,case when count(customer_id)over(partition by customer_id)=1 then 1
         else 
              (row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership,t.customer_tel_id ))+1 
         end as flag 
         from t1 t
         
--现在需要将这些flag值对应赋予t1表里面的flag值(根据customer_tel_id关联)。我自己写了样例如下：但是执行不了。
update   t1 t1
set t1.flag=
    (        
         select customer_tel_id,case when count(customer_id)over(partition by customer_id)=1 then 1
         else 
              (row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership,t.customer_tel_id ))+1 
         end as num 
         from t1 t --where t.customer_id=t1.customer_id and t.customer_tel_id=t1.customer_tel_id
    )
问下，这样的需求用一句sql如何搞定呢？

Answer 1

我之所以用update....批量语句是考虑到生产环境里面t1表又700万条记录，用游标循环的话，是不行的，只能用update批量来坐了！但是这个批量sql不知道如何写！

Answer 2

update  t1 t1 
set t1.flag= 
    (   select flag from (     
        select customer_id,customer_tel_id,
         case when count(customer_id)over(partition by customer_id)=1 then 1 
         else 
        (row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership,t.customer_tel_id ))+1 
         end as flag 
        from t1 t) where customer_id=t1.customer_id and customer_tel_id=t1.customer_tel_id 
    );

Answer 3

建议建一个同样结构的中间表，先用select 语句将 T1 的所有数据和设置好的flag一起insert到中间表T2。(为何这里的customer_id有多个的时候从2开始计数？)
然后truncate原表T1，中间表T2的数据直接insert 回原表T1。

Answer 4

这个语句我会写，但是每次子查询都需要扫描全表，我生产库上t1表又700万条记录，这样一来，这条update语句会死掉的啊！

Answer 5

这是不可能的啊，因为就一条sql语句，customer_id值分组的时候就一条sql语句啊!

Answer 6

那建一个临时表？把update里面的select结果保存下来,同时在临时表的列customer_id和customer_tel_id上加联合索引

Answer 7

更新完后就把它删掉呀。写个procedure处理这个好了。
create or replace procedure proc
is
begin
   execute immediate 'create table tmp as 
                      select t.customer_id
                           , t.customer_tel_id
                           , case when count(customer_id)over(partition by customer_id)=1 then 1 
                                  else (row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership,t.customer_tel_id ))+1 
                             end as flag 
                      from   t1 t';   execute immediate 'create index idx1 on tmp(customer_id,customer_tel_id)';
   commit;
   
   execute immediate 'update  t1 t1 
                      set     t1.flag = (select flag 
                                         from   tmp 
                                         where  customer_id       = t1.customer_id
                                         and    customer_tel_id   = t1.customer_tel_id)';   execute immediate 'drop table tmp';
   commit;
exception
   when others then 
      rollback;
      raise;
end proc;
/

Answer 8

你为什么要全用动态sql来commit，我直接sql，然后commit不可以吗？

Answer 9

编译没法通过的，因为procedure编译时临时表还不存在于数据库中