with a as (select 5252 infoid,1 fid , 0 val from dual union all select 5252 infoid,1 fid , 0 val from dual union all select 5252 infoid,1 fid , 2 val from dual union all select 5252 infoid,2 fid , 1 val from dual union all select 5252 infoid,2 fid , 1 val from dual union all select 5252 infoid,2 fid , 3 val from dual union all select 5253 infoid,3 fid , 1 val from dual union all select 5253 infoid,3 fid , 0 val from dual union all select 5253 infoid,3 fid , 2 val from dual ) select a.infoid,a.fid||'.'||row_number() over(partition by a.infoid ,a.fid order by a.val) fid,a.val from a ; 拼凑下勒
-- 大体如下,未测试
select infoid,
cast(fid as varchar(20)) + '.' + cast(ROW_NUMBER() over(partition by fid order by fid) as varchar(20)),
val
from 表A
每个infoid 对应有3条记录,3条记录都对应同一个pid,我现在想要把查询结果pid,根据第1到第三的值,变为
1.1,
1.2,
1.3 这样子
as
(select 5252 infoid,1 fid , 0 val from dual union all
select 5252 infoid,1 fid , 0 val from dual union all
select 5252 infoid,1 fid , 2 val from dual union all
select 5252 infoid,2 fid , 1 val from dual union all
select 5252 infoid,2 fid , 1 val from dual union all
select 5252 infoid,2 fid , 3 val from dual union all
select 5253 infoid,3 fid , 1 val from dual union all
select 5253 infoid,3 fid , 0 val from dual union all
select 5253 infoid,3 fid , 2 val from dual
)
select a.infoid,a.fid||'.'||row_number() over(partition by a.infoid ,a.fid order by a.val) fid,a.val
from a ;
拼凑下勒